Correct syntax for mysql JSON path to traverse arrays?

Question

My question is about searching through the contents of json arrays when searching in mysql's JSON data type.

DB Structure

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

Things that work

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.

My Problem

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?

Answer 1

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;

Answer 2

i think u want that

SELECT `id` FROM `jsontest` WHERE JSON_CONTAINS(`foo`->"$.items[*].type", '"bar"', '$')