CODEWITHSUNDEEP
mysqljson
Ramakrishna
Ramakrishna

Reputation: 1

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

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.

Upvotes: 0

Views: 175

Answers (1)

Barmar
Barmar

Reputation: 782498

$[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.

Upvotes: 1

Related Questions