Select Objects from Array of Objects that match a property in MYSQL JSON

Question

I have a table with 1 JSON type column city in a MySQL database that stores a JSON array of city objects with following structure:

{
    "cities": [
        {
            "id": 1,
            "name": "Mumbai",            
            "countryID": "9"
        },
        {
            "id": 2,
            "name": "New Delhi",            
            "countryID": "9"
        },
        {
            "id": 3,
            "name": "Abu Dhabi",            
            "countryID": "18"
        }
    ]
}

I want to select objects from the cities array having countryID = 90 but I am stuck as the array of objects is stored in a single column city which is preventing me from doing a (*) with WHERE JSON_CONTAINS(city->'$.cities', JSON_OBEJECT('countryID', '90')).

My query looks like this and I am not getting anywhere,

SELECT JSON_EXTRACT(city, '$.cities') FROM MyTable WHERE JSON_CONTAINS(city->'$.cities', JSON_OBJECT('countryID', '90'))

It'd be a great help if someone can point me in right direction or gimme a solution to this.

Thanks

Kiran Muralee · Answer 1 · 2018-10-26T18:22:49.383

If you are using MySQL 8.0, there is a feature called JSON table functions. It converts JSON data into tabular form.Then onward you can filter the result.

The query to acheive the same is given below

Select  country
 FROM json_cal,
   JSON_TABLE(
     city,
    "$.cities[*]" COLUMNS(
     country JSON PATH "$",
     NESTED PATH '$.countryID' COLUMNS (countryID TEXT PATH '$')          
     )
   ) AS  jt1 
where countryID = 90;

The DB Fiddle can be found here

More information on JSON Table functions can be found here

Select Objects from Array of Objects that match a property in MYSQL JSON

1 Answers1