Skip to content
Advertisement

Mysql Json extract with conditional filter

I’m trying to query some json data through a filter. Given a json array like this:

I’d like to select all the rows which have as country name ‘France’ and a value greater than 10 as ‘people’ ONLY in objects which have the property name set to ‘France’.

Would it be possible through Mysql JSON functions?

Thank you very much

Advertisement

Answer

In the first place, that isn’t valid JSON, so none of the JSON functions in MySQL will work. In valid JSON, you can’t use single-quotes like ' to delimit keys or strings. You must use double-quotes like ".

Also keys must be delimited with double-quotes, not just values.

So your data should look like this:

If we load it into a table:

Then we can use MySQL 8.0’s JSON_TABLE() function:

Output:

Then we can search it as if it’s a normal table:

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement