Search code examples
phpmysqljsonmysql-json

JSON_CONTAINS unable to find from MySQL

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.

Solution

  • It returns the row after writing the below codes:

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