My question is about searching through the contents of json arrays when searching in mysql's JSON data type.
So, if i have two rows in a mysql table, with a json field, called foo
.
The first row has:
{
"items": [
{"type": "bar"}
]
}
The second row has:
{
"items": [
{"type": "baz"}
]
}
I can run
select `foo`->"$.items[0].type" from `jsontest`
to return 2 results: bar
and baz
I can run
select `id` from `jsontest` where `foo`->"$.items[0].type" = "bar"
to return 1 result: 1
- ie. the id of the first row.
The mysql docs state that you can use [*]
to "evaluate to the values of all elements in a JSON array".
However, the following query returns zero items:
select `id` from `jsontest` where `foo`->"$.items[*].type" = "bar"
What is wrong with my query?
Make the following query:
select id, `foo`->"$.items[*].type[0]" from `jsontest`;
You'll notice returned value is displayed as "[bar]", which is JSON array.
select * from `jsontest`
where `foo`->"$.items[*].type" = JSON_ARRAY('bar');
Anyway, the following query should work too:
select id from `jsontest` where JSON_SEARCH(`foo`, 'all','bar') is not null;