Re: Issue with searching for object property in array for JSON data type
Posted by: Alfredo Kengi Kojima
Date: December 02, 2016 12:41PM

Note that your sample query returned a list of values.
Comparing ["123", "foo"] = "123" would not match.

What you're probably looking for is something like JSON_CONTAINS(), which lets you check whether a value is contained in a JSON array. However, there is one more complication, which is that JSON_CONTAINS() assumes a JSON value. A JSON string is represented as a quoted string, which means that if you're using a literal SQL string in your query, you must double quote it using JSON_QUOTE(). So in the end, your sample query would have to look like this:

mysql-sql> select * from people where json_contains(value->'$.friends[*].friendId', json_quote("123"));
+---------------------------------------------------------------------------+
| value |
+---------------------------------------------------------------------------+
| {"key1": "value1", "friends": [{"friendId": "123"}, {"friendId": "foo"}]} |
+---------------------------------------------------------------------------+
1 row in set (0.01 sec)

--
Alfredo Kojima
MySQL Developer Tools

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Issue with searching for object property in array for JSON data type
789
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.