Mysql Json extract with conditional filter

Question

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 1

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 |
+----+-------+--------------+----------------+