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:
None 
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
Description:
http://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html says:

"
Returns 0 or 1 to indicate whether a specific value is contained *anywhere* in a target JSON document, or, if a path argument is given, at a specific path within the target document."

Clearly 2 is contained withing the document below. But it's not being found by the function if I omit the path.

How to repeat:
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.7.10-debug |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_CONTAINS( '{"a": 1, "b": 2 }', '2');
+------------------------------------------+
| JSON_CONTAINS( '{"a": 1, "b": 2 }', '2') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Do as the Docs Say :)
[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.