how to use WHERE in an array of JSON data in MYSQL-CodePudding

how to use WHERE in an array of JSON data in MYSQL

I have a simple table myTable having one column with JSON datatype.

create table myTable(profile JSON default NULL);

then I inserted this record

insert into myTable values ('[{"name":"","type":"student","age":""},
{"name":"","type":"teacher","age":"240"},
{"name":"","type":"student","age":"25"},
{"name":"","type":"student","age":"20"}]')

my question is I want to retrieve all records names who are a student and age between 20 & 25?

is it possible to use it like this? SELECT name FROM myTable WHERE type = "student' and age BETWEEN 20 &2 5

CodePudding user response：

SELECT jsontable.name, jsontable.age 
FROM myTable
CROSS JOIN JSON_TABLE(myTable.profile,
                      '$[*]' COLUMNS (name VARCHAR(255) PATH '$.name',
                                      `type` VARCHAR(255) PATH '$.type', 
                                      age INT PATH '$.age')) jsontable
WHERE jsontable.`type` = 'student'
  AND jsontable.age BETWEEN 20 AND 25;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=559ea1476f933a7ac977537e434f3206

CodePudding user response：

There is a complete chapter on JSON functions, and how they can be used.

select * 
from (
   select 
      x.x, 
      json_unquote(json_extract(profile,concat("$[",x.x,"].name"))) as name, 
      json_unquote(json_extract(profile,concat("$[",x.x,"].type"))) as type, 
      json_unquote(json_extract(profile,concat("$[",x.x,"].age")))  as age
   from myTable
   cross join (select 0 as x union select 1 union select 2 union select 3 union select 4) x
) t 
where t.type='student' and t.age between 20 and 25;