Issue with searching for object property in array for JSON data type
Posted by: Glide K
Date: September 07, 2016 06:19PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Issue with searching for object property in array for JSON data type
1180
September 07, 2016 06:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.