Bug #89049 JSON_EXTRACT('{}', '$[0][0]') shuld return NULL
Submitted: 24 Dec 2017 17:41 Modified: 2 Jan 2018 14:01
Reporter: Artem Zaytsev Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7/8.0 OS:Any
Assigned to: CPU Architecture:Any

[24 Dec 2017 17:41] Artem Zaytsev
Description:
JSON_EXTRACT('{}', '$[0][0]') returns '{}' instead of NULL

How to repeat:
```
mysql> SELECT JSON_EXTRACT('{}', '$[0]');
+-------------------------------+
| JSON_EXTRACT('{}', '$[0]')    |
+-------------------------------+
| {}                            |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('{}', '$[0][0]');
+-------------------------------+
| JSON_EXTRACT('{}', '$[0][0]') |
+-------------------------------+
| {}                            |
+-------------------------------+
1 row in set (0.00 sec)

```

But '$[1]' is working correctly:

```
mysql> SELECT JSON_EXTRACT('{}', '$[1]');
+-------------------------------+
| JSON_EXTRACT('{}', '$[1]')    |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0.00 sec)
```

Suggested fix:
Expected behaviour is 

```
mysql> SELECT JSON_EXTRACT('{}', '$[0][0]');
+-------------------------------+
| JSON_EXTRACT('{}', '$[0][0]') |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0.00 sec)
```
[24 Dec 2017 20:08] MySQL Verification Team
Thank you for the bug report.
[2 Jan 2018 8:53] Knut Anders Hatlen
This is actually intentional behaviour. See https://dev.mysql.com/doc/refman/5.7/en/json.html#json-paths:

"If path does not select an array value, path[0] evaluates to the same value as path"

The observed behaviour is a result of applying this rule twice. Since $ evaluates to {}, which is not an array, $[0] also evaluates to {}. Since $[0] evaluates to {}, which is not an array, $[0][0] also evaluates to {}.
[2 Jan 2018 10:08] Artem Zaytsev
Sorry, my mistake.

But what is the reason for this behavior? In my opinion, this is not obvious, inconsistent and creates inconvenience if the structure of the document is not strict. When you really need to test the existence of a path, you need to add additional conditions.

For example, to test the existence of '$ .a [0] .b [0] .c':

JSON_TYPE (JSON_EXTRACT (@json, '$ .a')) = 'ARRAY' AND
JSON_TYPE (JSON_EXTRACT (@json, '$ .a [0] .b')) = 'ARRAY' AND
JSON_EXTRACT (@json, '$ .a [0] .b [0] .c') IS NOT NULL

The situation is even more complicated if the path is dynamic. Maybe there is a simpler solution?
[2 Jan 2018 12:40] Knut Anders Hatlen
In the SQL standard the paths can be either strict or lax. In lax mode, non-arrays are wrapped in an array if the JSON path requires an array, which leads to the behaviour where path and path[0] match the same value. I guess this mode is provided as a convenience to allow storing single values in fields that could take multiple values, without requiring them to be inserted as single-element arrays.

For example, if a person can have multiple phone numbers, it allows you to have documents such as

{ "name": "Knut", "phone": 1234 }

{ "name": "Knut", "phone": [1234] }

{ "name": "Knut", "phone": [1234, 5678] }

and be able to access the primary phone number using the path '$.phone[0]' without first checking if the phone number is an array or not.

The SQL standard allows you to specify whether the path is strict or lax, by saying 'strict $.phone[0]' or 'lax $.phone[0]' instead of just '$.phone[0]', but MySQL hasn't implemented this part of the SQL/JSON path language yet.
[2 Jan 2018 14:01] Artem Zaytsev
Thanks for the explanation. Are there any plans to implement strict-mode?
If not, then I can try to make a patch with this functionality, just tell me which SQL standard describes the work with JSON. I found strict and lax only in MS SQL Server.
[3 Jan 2018 8:47] Knut Anders Hatlen
I'm not aware of any immediate plans to implement strict mode. It is described in SQL:2016, Part 2: SQL/Foundation, section 9.39 SQL/JSON path language: syntax and semantics.