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?
Subject
Views
Written By
Posted
Issue with searching for object property in array for JSON data type
1297
September 07, 2016 06:19PM
845
December 02, 2016 12:41PM
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.