Bug #79231 | JSON_CONTAINS() does not work without a path | ||
---|---|---|---|
Submitted: | 11 Nov 2015 14:26 | Modified: | 2 Dec 2015 12:38 |
Reporter: | Georgi Kodinov | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
Version: | 5.7+ | OS: | Any |
Assigned to: | Jon Stephens | CPU Architecture: | Any |
[11 Nov 2015 14:26]
Georgi Kodinov
[11 Nov 2015 15:34]
MySQL Verification Team
Thank you for the bug report. C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.10 Source distribution PULL: 2015-NOV-07 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > SELECT JSON_CONTAINS( '{"a": 1, "b": 2 }', '2'); +------------------------------------------+ | JSON_CONTAINS( '{"a": 1, "b": 2 }', '2') | +------------------------------------------+ | 0 | +------------------------------------------+ 1 row in set (0.05 sec)
[12 Nov 2015 12:14]
Knut Anders Hatlen
I think the implementation works the way it is documented. Specifically, the section referred to in the bug description says: --- The following rules define containment: A candidate scalar is contained in a target scalar if and only if they are comparable and are equal. Two scalar values are comparable if they have the same JSON_TYPE() types, with the exception that values of types INTEGER and DECIMAL are also comparable to each other. A candidate array is contained in a target array if and only if every element in the candidate is contained in some element of the target. A candidate nonarray is contained in a target array if and only if the candidate is contained in some element of the target. A candidate object is contained in a target object if and only if for each key in the candidate there is a key with the same name in the target and the value associated with the candidate key is contained in the value associated with the target key. --- Since we have a scalar candidate, only the first and third rules apply. A scalar can only be contained in a scalar document or in an array. Since the target document is an object, the candidate is not contained in the target document according to those rules.
[12 Nov 2015 13:03]
Knut Anders Hatlen
It would perhaps be clearer if the documentation dropped the word "anywhere".
[13 Nov 2015 14:40]
Dag Wanvik
Posted by developer: Changed category to Docs, to remove the confusing word "anywhere".
[17 Nov 2015 16:24]
Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.
[30 Nov 2015 16:08]
Georgi Kodinov
Ok, I get the type difference argument. But how exactly do I then get the 2 from the above sample ? I've tried the following: mysql> SELECT JSON_CONTAINS( '{"a": 1, "b": 2 }', '2'); -------------- SELECT JSON_CONTAINS( '{"a": 1, "b": 2 }', '2') -------------- +------------------------------------------+ | JSON_CONTAINS( '{"a": 1, "b": 2 }', '2') | +------------------------------------------+ | 0 | +------------------------------------------+ mysql> select json_type('2'); -------------- select json_type('2') -------------- +----------------+ | json_type('2') | +----------------+ | INTEGER | +----------------+ mysql> select json_type(json_extract('{"a": 1, "b": 2 }', '$.b')); -------------- select json_type(json_extract('{"a": 1, "b": 2 }', '$.b')) -------------- +-----------------------------------------------------+ | json_type(json_extract('{"a": 1, "b": 2 }', '$.b')) | +-----------------------------------------------------+ | INTEGER | +-----------------------------------------------------+
[1 Dec 2015 15:33]
Dag Wanvik
There is currently no way to search for an arbitrary JSON value (including an INTEGER scalar) located at an arbitrary location inside another document. There is a method to locate a JSON string, i.e. JSON_SEARCH: JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) Returns the path to the given string within a JSON document. If the possible path(s) are enumerable, one can use JSON_EXTRACT(s) and test the extracted value for INTEGER-ness and the desired value.