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?

Parse the array of objects to separate objects (use JSON_TABLE() function), check each object's property, then aggregate matched objects back. — Akina
– Akina, Commented Oct 8, 2020 at 11:19
@Akina Thanks for your response. Can you explain it via code? — sundowatch
– sundowatch, Commented Oct 8, 2020 at 11:21

Akina · Accepted Answer · 2020-10-08 12:17:21Z

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.

edited Oct 8, 2020 at 12:17

answered Oct 8, 2020 at 11:38

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

sundowatch Over a year ago

Is it valid for MySQL?

Akina Over a year ago

@sundowatch Have you seen the fiddle by link? it is executed in MySQL 8.0.21 successfully. But if the version is ancient then it will not work - JSON_TABLE was implemented in 8.0.4.

sundowatch Over a year ago

Hmm, ok. I'm using 5.6.43. Is there any equevelant for this?

Akina Over a year ago

@sundowatch For this version you must use common string functions.

sundowatch Over a year ago

Do you mean LIKE or any other common string functions?

|

Collectives™ on Stack Overflow

How to search through in JSON array on SQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related