Using MariaDb, how can I query a json(longtext) column to check if a given value exists under any key?

Question

I have this json data in a 'name' column, where the names of some records are stored with multi-lang support:

{"en":"Professional Association","de":"Berufsverband","uk":null}

When I run this query returns 0 records:

select * from `some_table` where lower(json_value(name,'$.*')) like lower('%Berufsverband%');

But if I run the query specifying the lang key (de) it works:

select * from `some_table` where lower(json_value(name,'$.de')) like lower('%Berufsverband%');

My question is, how can I properly use the $.* wildcard on MariaDB?

If I run the exact same query on MySQL it works fine:

select * from `some_table` where lower(json_unquote(name->'$.*')) like lower('%Berufsverband%');

Answer 1

You can use a combination of JSON_CONTAINS and JSON_EXTRACT. You cannot use wildcards within JSON_CONTAINS, but you can within JSON_EXTRACT, so, if you wrap the contains with an extract using a wild card (see Object Member Selector in JSONPath Expression below) you will return the rows containing the value you're searching for.

Object Member Selector

To select member(s) in a JSON object, one can use one of the following:

• .memberName selects the value of the member with name memberName.
• ."memberName" - the same as above but allows one to select a member with a name that's not a valid identifier (that is, has space, dot, and/or other characters)
• .* - selects the values of all members of the object.

select * from `some_table` 
WHERE JSON_CONTAINS(JSON_EXTRACT(name, "$.*"), '"Berufsverband"')

db<>fiddle here.

Answer 2

I just made it work, I replaced json_value with json_query. The result was something like this:

select * from `some_table` where lower(json_query (name, '$')) like lower('%Berufsverband%');

Now it works as expected.