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


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":""},

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


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

select * 
from (
      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)

Leave a Reply

Your email address will not be published. Required fields are marked *