JSON_CONTAINS unable to find from MySQL

Question

In MySQL I'm storing the data within "jsonValues" field with PHP's json_encode:

{"product_category":[[{"category":["28"]},{"product":["16","22","64"]}]]}

Through PHP, I want to get the data by writing the following query:

SELECT * FROM `dbTable` 
WHERE JSON_CONTAINS(jsonValues, '"category":["28"]');

However, it shows the error:

3141 - Invalid JSON text in argument 2 to function json_contains: "The document root must not follow by other values." at position 10.

Why are you storing JSON in your DB? Can't you store them in a proper DB schema? `JSON_CONTAINS` is probably not what you want, you might be searching for `JSON_EXTRACT`. See https://stackoverflow.com/a/39163928/3223157 on how to use it. — Xatenev, Aug 17 '18 at 06:13
@Xatenev I can't because the information which I need to be stored can't be stored into the proper db schema as these are not identical. — Niladri Banerjee - Uttarpara, Aug 17 '18 at 06:15
Have a try of something like this `SELECT * FROM 'dbTable' WHERE JSON_CONTAINS(jsonValues, '$.category');` Reference:[https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-contains] — Hidayat Ullah, Aug 17 '18 at 06:30
I already tried but returns nothing. `SELECT * FROM `dbTable` WHERE JSON_CONTAINS(jsonValues, '["28"]', '$.shipping_location');` — Niladri Banerjee - Uttarpara, Aug 17 '18 at 06:34
Have you tried this way `SELECT * FROM 'dbTable' WHERE JSON_CONTAINS(jsonValues, '{"category":"28"}');` — Hidayat Ullah, Aug 17 '18 at 06:38

score 3 · Answer 1 · answered Aug 17 '18 at 06:18

3

Have you tried adding the curly brackets around your search term, as the mysql doc does?

SELECT * FROM `dbTable` WHERE JSON_CONTAINS(jsonValues, '{"category":["28"]}');

answered Aug 17 '18 at 06:18

Zim84

Thanks for this but it also returns nothing. – Niladri Banerjee - Uttarpara Aug 17 '18 at 06:20
Is anything wrong in my jsonValues as mentioned in my question? – Niladri Banerjee - Uttarpara Aug 17 '18 at 06:23

score 1 · Accepted Answer · answered Aug 17 '18 at 06:38

1

It returns the row after writing the below codes:

SELECT * FROM `dbTable` WHERE JSON_CONTAINS(jsonValues, '{"category":["28"]}', '$.product_category');

answered Aug 17 '18 at 06:38

score 1 · Answer 3 · answered Aug 17 '18 at 06:48

1

If None of the above method works for you then try below code it should work.

SELECT * FROMdbTableWHERE JSON_CONTAINS(jsonValues, '{"product_category": [[{"category": ["28"]}]]}');

answered Aug 17 '18 at 06:48

Hidayat Ullah

score 1 · Answer 4 · answered Aug 29 '21 at 13:17

i was having the same problem, i was saving data as follows in the DB,

$id = '1';
json_encode(array($id)); //in DB it stores like this => ["1"]

I changed this to

$id = '1';
json_encode(array(intval($id))); //in DB it stores like this => [1]

Then this query works perfectly.

Select * from table where WHERE JSON_CONTAINS(json_col, '$id');

I hope it will help someone in future.

3141 - Invalid JSON text in argument 2 to function json_contains: "The document root must not follow by other values." at position 10.