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

Question

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%'

Is that JSON valid? I dont think so! Does'nt it have to be?? I think so! — RiggsFolly, Dec 29 '20 at 11:52
This is not valid JSON. You should have square brackets for `"items"`, not curly brackets. `"items: [ {"name": "banana", "color": "yellow"}, ... ]`. — GMB, Dec 29 '20 at 11:52
Imagine that JSON contains 2 fruits with red color in one JSON value - what output do you want to see in this case? — Akina, Dec 29 '20 at 11:56
How did you create the JSON you inserted into your database? It looks like that code need to be fixed first — RiggsFolly, Dec 29 '20 at 11:58
I'm SORRY !!! I created on the fly ... it's not valid, SORRY but it's just to get the idea — FlamingMoe, Dec 29 '20 at 12:04

GMB · Accepted Answer · 2020-12-29T12:31:31.387

1

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'

edited Dec 29 '20 at 12:31

answered Dec 29 '20 at 11:51

GMB

216,147
25
84
135

I'm running MariaDb 10.5 ... but it does not work I think :-( – FlamingMoe Dec 29 '20 at 12:26
@FlamingMoe: ah ok... You originally tagged your question MySQL (there is a separate MariaDB tag), hence my answer. See my edit for a MariaDB-compatible solution. – GMB Dec 29 '20 at 12:32

score 0 · Answer 2 · answered Dec 29 '20 at 17:53

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

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

2 Answers2