Reputation: 123
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
Reputation: 2060
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