CODEWITHSUNDEEP
mysqljsonmysql-json
Shyam Singh
Shyam Singh

Reputation: 123

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

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

Upvotes: 2

Views: 2132

Answers (1)

Kiran Muralee
Kiran Muralee

Reputation: 2070

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

Upvotes: 1

Related Questions