json_contains issue with the query, like

I try to find a food with some categories stored in the categories column as json.

My query : select * fromfoodswhere json_contains(categories, '["Aliments"]')

this should return at list one element who have in categories column is content :

[
    "Aliments et boissons à base de végétaux",
    "Aliments d'origine végétale",
    "Matières grasses",
    "Matières grasses végétales",
    "Huiles"
]

​x
 
[    "Aliments et boissons à base de végétaux",    "Aliments d'origine végétale",    "Matières grasses",    "Matières grasses végétales",    "Huiles"]​

what i’m doing wrong with the query ?

Answer

JSON_CONTAINS does not work as LIKE, it just looks for exact values. You should first extract the field and then run a LIKE query against. Since you are searching in root (according the data you gave) you need something like;

select * 
from foods 
where json_extract(categories,'$') LIKE '%Aliments%'

 
select * from foods where json_extract(categories,'$') LIKE '%Aliments%'​

Advertisement

Answer