How to search through in JSON array on SQL

Question

I'm using MySQL 5.6.43 and trying to search through in JSON array in a table. My JSON in MySQL database is like this:

[
    {
        "Name": "AAA",
        "CountryCode": "AFG",
        "District": "Kabol",
        "Population": 1780000
    },
    {
        "Name": "BBB",
        "CountryCode": "AFG",
        "District": "Qandahar",
        "Population": 237500
    },
    {
        "Name": "CCC",
        "CountryCode": "USD",
        "District": "Qandahar",
        "Population": 237500
    }
]

I want to take the just AFG CountryCode's. So my result should be like this:

[
    {
        "Name": "AAA",
        "CountryCode": "AFG",
        "District": "Kabol",
        "Population": 1780000
    },
    {
        "Name": "BBB",
        "CountryCode": "AFG",
        "District": "Qandahar",
        "Population": 237500
    }
]

How can I achieve that?

Answer 1

SELECT test.data, JSON_ARRAYAGG(JSON_OBJECT('Name',jsontable.Name,
                                            'CountryCode',jsontable.CountryCode,
                                            'District',jsontable.District,
                                            'Population',jsontable.Population)) filtered
FROM test,
     JSON_TABLE(test.data,
                '$[*]' COLUMNS (Name VARCHAR(255) PATH '$.Name',
                                CountryCode VARCHAR(255) PATH '$.CountryCode',
                                District VARCHAR(255) PATH '$.District',
                                Population VARCHAR(255) PATH '$.Population')) jsontable
WHERE jsontable.CountryCode = 'AFG'
GROUP BY test.data;

fiddle

In MySQL 5.6 you must use string functions - JSON is not implemented in this version yet:

SELECT CONCAT('[', GROUP_CONCAT('{', SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(test.data, '}', nums.num), '}', -1), '{', -1), '}'), ']') filtered
FROM test
CROSS JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) nums
WHERE LOCATE('"CountryCode":"AFG"', SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(test.data, ' ', ''), '}', nums.num), '}', -1))
GROUP BY test.data

fiddle

If source array may contain more than 4 objects per value then expand nums subquery.

Restriction: Neither key nor value to be searched must contain a space.