Issue with searching for object property in array for JSON data type

Question

I have the following schema with value as JSON type

mysql> select * from people;
+------+---------------------------------------------------------------------------+
| id   | value                                                                     |
+------+---------------------------------------------------------------------------+
| blah | {"key1": "value1", "key2": "value2"}                                      |
| foo  | {"key1": "value1", "friends": [{"friendId": "123"}, {"friendId": "foo"}]} |
+------+---------------------------------------------------------------------------+

I expected the query below to return me row foo but it did not.

mysql> select * from people where value->'$.friends[*].friendId' = "123";
Empty set 

The condition value->'$.friends[*].friendId' seems valid since it works for the query below:

mysql> select value->'$.friends[*].friendId' from people;
+---------------------------------+
| value->'$.friends[*].friendId' |
+---------------------------------+
| NULL                            |
| ["123", "foo"]                  |
+---------------------------------+

So how come the query select * from people where value->'$.friends[*].friendId' = "123"; returns no results?

Answer 1

Using JSON_CONTAINS with the JSON array value I'm interested in works:

select * from people where JSON_CONTAINS (value, {"friends": [{"friendId": "123"}]});