MySQL Bugs: #79231: JSON_CONTAINS() does not work without a path

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

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 :)

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)

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.

It would perhaps be clearer if the documentation dropped the word "anywhere".

Posted by developer:
 
Changed category to Docs, to remove the confusing word "anywhere".

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

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                                             |
+-----------------------------------------------------+

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.