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"
]

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%'