CODEWITHSUNDEEP
sqlarraysjsonmariadbmariadb-10.5
FlamingMoe
FlamingMoe

Reputation: 2992

How to select a field of a JSON object coming from the WHERE condition

I have this table

id  name   json
1   alex   {"type": "user", "items": [ {"name": "banana", "color": "yellow"}, {"name": "apple", "color": "red"} ] }
2   peter  {"type": "user", "items": [ {"name": "watermelon", "color": "green"}, {"name": "pepper", "color": "red"} ] }
3   john   {"type": "user", "items": [ {"name": "tomato", "color": "red"} ] }
4   carl   {"type": "user", "items": [ {"name": "orange", "color": "orange"}, {"name": "nut", "color": "brown"} ] }

Important, each json object can have different number of "items", but what I need is the "product name" of JUST the object that matched in the WHERE condition.

My desired output would be the two first columns and just the name of the item, WHERE the color is like %red%:

id name  fruit
1  alex  apple
2  peter pepper
3  john  tomato

select id, name, ***** (this is what I don't know) FROM table
where JSON_EXTRACT(json, "$.items[*].color") like  '%red%'

Upvotes: 0

Views: 149

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You can use JSON_EXTRACT() function along with Recursive Common Table Expression in order to generate rows dynamically such as

WITH RECURSIVE cte AS 
(
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1
    FROM cte
   WHERE cte.n < (SELECT MAX(JSON_LENGTH(json)) FROM t )
)
SELECT id, name, 
       JSON_UNQUOTE(JSON_EXTRACT(json,CONCAT('$.items[',n-1,'].name'))) AS fruit
  FROM cte
  JOIN t
 WHERE JSON_EXTRACT(json,CONCAT('$.items[',n-1,'].color')) = "red"

Demo

Upvotes: 0

GMB
GMB

Reputation: 222672

I would recommend json_table(), if you are running MySQL 8.0:

select t.id, t.name, x.name as fruit
from mytable t
cross join json_table(
    t.js,
    '$.items[*]' columns (name varchar(50) path '$.name', color varchar(50) path '$.color')
) x
where x.color = 'red'

This function is not implemented in MariaDB. We can unnest manually with the help of a numbers table:

select t.id, t.name, 
    json_unquote(json_extract(t.js, concat('$.items[', x.num, '].name'))) as fruit
from mytable t
inner join (select 0 as num union all select 1 union all select 2 ...) x(num)
    on x.num < json_length(t.js, '$.items')
where json_unquote(json_extract(t.js, concat('$.items[', x.num, '].color'))) = 'red'

Upvotes: 1

Related Questions