Unable to write WHERE condition for a JSON array field with array values in MySQL

Question

I have a JSON datatype with array values in a MySQL database as highlighted in the picture below:

enter image description here

I want to compare multiple values with this category field, Ex: ["49","27"].

How to write a MySQL query for this?

I tried this query:

SELECT l.*, pc.name as cat_name,u.name as uname
FROM listing l
LEFT OUTER JOIN package_purchased_history ph ON ph.user_id = l.user_id AND ph.expired_date >= 1656095400 AND ph.purchase_date <= 1656095400
LEFT OUTER JOIN user u ON u.id = l.user_id
INNER JOIN category pc ON JSON_SEARCH(l.categories, 'one', pc.id) AND pc.parent = 26
WHERE JSON_CONTAINS(l.categories,'["49"]','$[0]') IS NOT NULL
AND l.status = 'active'
GROUP BY l.id

Unfortunately it is not working, so please suggest me the a better approach.

Please spend some time writing a good, polished and understandable post. It will help others help you effectively. — lemon
– lemon, Commented Jun 25, 2022 at 14:16
The JSON data to be found in the array is string (["49","27"]) whereas you search for numeric value in your code ([49]) which won't be found. Search for string value ["49"]... — Akina
– Akina, Commented Jun 25, 2022 at 16:00

Barmar · Accepted Answer · 2022-06-25 15:49:19Z

1

$[0] is the first element of the array, not the whole array. So you're testing whether an array is contained in a single number, not whether the array is contained in the array.

The whole array is $, since that refers to the top-level element of the JSON value. But you don't need to specify the path when you're searching the whole value.

WHERE JSON_CONTAINS(l.categories, '[49]')

You don't need IS NOT NULL there, since JSON_CONTAINS() returns a boolean. The value will never be NULL unless l.categories is NULL.

answered Jun 25, 2022 at 15:49

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

Unable to write WHERE condition for a JSON array field with array values in MySQL

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