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: | |
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
[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.