MySQL : Use JSON_ARRAY content in SELECT IN query

Question

I got the two following tables

In the 'items' table, desc column is a JSON array

Table items

+--------+--------+-------+
|item_id | name   | json  |
+--------+--------+-------+
| 1      | Item 1 | [1,2] |
+--------+--------+-------+

Table descriptions

+----+-------------+
| id | description |
+----+-------------+
| 1  | First desc  |
+----+-------------+
| 2  | Second desc |
+----+-------------+

I would like to retrieve all the descriptions for a prticular id.

I have tried this, with no luck:

SELECT id, description 
FROM descriptions 
WHERE id IN (SELECT JSON_EXTRACT(json, '$[*]') 
             FROM items 
             WHERE item_id=1)

dev.mysql.com/doc/refman/8.0/en/json-search-functions.html might help you — half of a glazier
– half of a glazier, Commented Feb 4, 2020 at 13:32

GMB · Accepted Answer · 2020-02-04 13:34:54Z

1

You can use JSON function json_search(), which is available from MySQL 5.7 onwards:

select i.item_id, i.name, d.id, d.description
from items i
inner join descriptions d on json_search(i.json, 'one', d.id) is not null 
where i.item_id = 1

answered Feb 4, 2020 at 13:34

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

MySQL : Use JSON_ARRAY content in SELECT IN query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related