MySQL using JSON_ARRAYAGG in a SELECT IN clause?

Question

Our database solution is very JSON heavy, and as such, our SQL queries are all JSON based (for the most part). This includes extensive use of JSON_ARRAYAGG().

The problem I'm encountering is using a returned array of indexes in WHERE IN, which simply doesn't work. From what I can tell it's a simple formatting issue where MySQL wants an () encapsulation and a JSON array is a [] encapsulation.

For example:

SELECT COUNT(si.ID) AS item_count, JSON_ARRAYAGG(si.ID) AS item_array
FROM sourcing_item si;

Returns:

7, [1,2,3,4,5,6,7]

What I need to do is write a complex nested query that allows for selecting record IDs that are IN the JSON_ARRAYAGG result. Like:

SELECT si.item_name
FROM sourcing_item si
WHERE si.ID IN item_array

Of course the above doesn't work because MySQL doesn't recognize [] vs. ().

Is there a viable workaround for this issue? I'm surprised they haven't updated MySQL to allow the WHERE IN clause to work with a JSON array...

Barmar · Accepted Answer · 2021-08-26 17:44:57Z

2

The MEMBER OF operator does this.

SELECT si.item_name
FROM sourcing_item si
WHERE si.ID MEMBER OF (item_array)

answered Aug 26, 2021 at 17:44

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

12 Comments

Barmar Over a year ago

It's relatively new, it was added in MySQL 8.0.

Barmar Over a year ago

If you have a subquery, why do you need to use JSON_ARRAYAGG()? Just use WHERE sp.sourcing_search_ref_id IN (SELECT ss.ID FROM ...)

Barmar Over a year ago

Try using JOIN instead of IN. That will allow you to pass multiple columns up.

Barmar Over a year ago

All those nested INs should just be a join between all 3 tables.

Barmar Over a year ago

In general, SELECT * FROM A WHERE A.col1 IN (SELECT col2 FROM B) can be transformed to SELECT A.* FROM A JOIN B ON A.col1 = B.col2

|

Collectives™ on Stack Overflow

MySQL using JSON_ARRAYAGG in a SELECT IN clause?

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related