[SOLVED] how to use WHERE in an array of JSON data in MYSQL

Issue

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

Solution

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;

Answered By – Luuk

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)