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

Question

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

Would it be possible to create a normal database table, using name, type and age as columns, instead of putting this in one column using a JSON string? — KIKO Software
– KIKO Software, Commented Mar 16, 2022 at 8:57

Akina · Accepted Answer · 2022-03-16 09:04:09Z

2

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

answered Mar 16, 2022 at 9:04

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

KIKO Software Over a year ago

I would call this a very good illustration of why using JSON data in MySQL is a bad idea.

Akina Over a year ago

@KIKOSoftware i think that using JSON (CSV, XML and so on) is bad idea in any DBMS when there are any operations except save and retrieve with this value. It contradicts normal forms in most cases.

KIKO Software Over a year ago

I agree completely.

Luuk · Accepted Answer · 2022-03-16 09:21:04Z

0

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 Mar 16, 2022 at 9:21

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related