Mysql Json extract with conditional filter

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

[ {name:'name1', country:[{name:'France', people:10}, {name:'Japan',people:20}]}, {name:'name2', country:[{name:'France', people:20}, {name:'Japan',people:40}]}]

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

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:

[
  {
    "name": "name1",
    "country": [
      {
        "name": "France",
        "people": 10
      },
      {
        "name": "Japan",
        "people": 20
      }
    ]
  },
  {
    "name": "name2",
    "country": [
      {
        "name": "France",
        "people": 20
      },
      {
        "name": "Japan",
        "people": 40
      }
    ]
  }
]

If we load it into a table:

create table mytable (id serial primary key, data json);
insert into mytable set data = '...JSON based on the above...';

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

select mytable.id, j.* 
from mytable, json_table(mytable.data, '$[*]' columns (
  name varchar(20) path '$.name',
  nested path '$.country[*]' columns (
    country_name varchar(20) path '$.name', 
    country_people int path '$.people')
  )
) as j

Output:

+----+-------+--------------+----------------+
| id | name  | country_name | country_people |
+----+-------+--------------+----------------+
|  1 | name1 | France       |             10 |
|  1 | name1 | Japan        |             20 |
|  1 | name2 | France       |             20 |
|  1 | name2 | Japan        |             40 |
+----+-------+--------------+----------------+

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

select mytable.id, j.*
from mytable, json_table(mytable.data, '$[*]' columns (
  name varchar(20) path '$.name',
  nested path '$.country[*]' columns (
    country_name varchar(20) path '$.name',
    country_people int path '$.people')
  )
) as j
where j.country_name = 'France' and j.country_people = 10;

+----+-------+--------------+----------------+
| id | name  | country_name | country_people |
+----+-------+--------------+----------------+
|  1 | name1 | France       |             10 |
+----+-------+--------------+----------------+