How to check for ANY like elements in JSON array in MySQL

Question

I have a row in table A of JSON array of integers.

I have a table B whose rows have a field of a (possibly empty) JSON array of objects, and each object has a 'parent' field.

  table A
+-----------+
| foo       |
+-----------+
| [1, 2]    |
+-----------+

  table B
+--------------------------------+
| bar                            |
+--------------------------------+
| [{"parent": 1}]                |
| [{"parent": 2}, {"parent": 3}] |
| [{"parent": 4}]                |
| []                             |
+--------------------------------+

How do I get the rows from table B whose objects in bar don't have any parent matching any element in foo? i.e. the last 2 rows here should be returned.

I tried doing something with SELECT JSON_EXTRACT(bar, '$[*].parent') FROM B, but the result is a JSON array

+-----------------------------------------+
| SELECT JSON_EXTRACT(bar, '$[*].parent') |
+-----------------------------------------+
| [1]                                     |
| [2, 3]                                  |
| [4]                                     |
| NULL                                    |
+-----------------------------------------+

and the function JSON_CONTAINS() doesn't return partial matches:

"A candidate array is contained in a target array if and only if every element in the candidate is contained in some element of the target." MySQL docs

Any help is greatly appreciated. MySQL version 5.7.22

Can you not do this comparison in your application's programming language? It honestly seems to me that you are going to have trouble doing it in mysql. See stackoverflow.com/questions/44767211/… — MandyShaw
– MandyShaw, Commented Jul 19, 2018 at 21:33
I'd prefer to have it done in pure mysql because I need to join the returned data to more stuff so I want to avoid having to loop and do another database fetch. Just wanted to see if anybody had a creative mysql-only solution — Alva
– Alva, Commented Jul 19, 2018 at 21:39
Have you considered denormalizing this instead of using JSON fields? You might get it to work, but it's not going to be fast. — Evert
– Evert, Commented Jul 19, 2018 at 21:51

Kevin Foster · Accepted Answer · 2023-09-14 01:00:22Z

0

A little late to the game on this, but you may be able to leverage mysql's native json parsing:

SELECT * FROM B WHERE B.json_field_name->'$.parent' NOT IN (CAST(SELECT A.field_name_containing_ints WHERE ...something that selects your integers... AS ARRAY))

Performance can be slow with large data sets, but I believe this is inline with what you're looking for. Sorry not to have any citations or docs on this - just coming out of my brain, but hopefully this is inline with what was needed, or can help someone else.

answered Sep 14, 2023 at 1:00

Kevin Foster

1792 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

How to check for ANY like elements in JSON array in MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related