CODEWITHSUNDEEP
mysqlsqlarraysjson
Mathieu Mourareau
Mathieu Mourareau

Reputation: 1220

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 ?

Upvotes: 1

Views: 536

Answers (2)

Tufan Barış Yıldırım
Tufan Barış Yıldırım

Reputation: 3962

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

Upvotes: 1

GMB
GMB

Reputation: 222492

JSON_CONTAINS() searches for exact match, and does not implement pattern matching.

Since you are using MySQL 8.0, you could use json_table() to unnest the array and then do pattern matching on each element:

select f.*
from foods f
where exists (
    select 1 
    from json_table(f.categories, "$[*]" columns(cat varchar(255) path "$")) c
    where c.cat like '%Aliments%'
)

Upvotes: 2

Related Questions