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

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