Bug #102233 JSON Path evaluation is not standard-compliant
Submitted: 12 Jan 2021 11:34 Modified: 12 Jan 2021 11:44
Reporter: Sergei Petrunia Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:8.0, 8.0.22 OS:Any
Assigned to: CPU Architecture:Any

[12 Jan 2021 11:34] Sergei Petrunia
Description:
(This originates from https://jira.mariadb.org/browse/MDEV-24573)

I am using an example from the publicly available portion of the SQL Standard
that describes JSON Path expressions.

Get this document
https://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017....

open it on page 70 (section 6.10.1 Member accessor), and read:

<quote>
Example: Suppose the context item is:

$ = { phones: [ { type: "cell", number: "abc-defg" },
                { number: "pqr-wxyz" },
                { type: "home", number: "hij-klmn" } ] }
$.phones.type is evaluated in lax mode as follows ...
</quote>

and then Table 35 shows that the result is "cell", "home".  MySQL returns NULL, instead.

How to repeat:
mysql> set @json='
    '> { "phones": [ { "type": "cell", "number": "abc-defg" },
    '>             { "number": "pqr-wxyz" },
    '>             { "type": "home", "number": "hij-klmn" } 
    '>           ] 
    '> }';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select json_extract(@json, '$.phones.type');
+--------------------------------------+
| json_extract(@json, '$.phones.type') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+
1 row in set (0.00 sec)
[12 Jan 2021 11:44] MySQL Verification Team
Hello Sergei,

Thank you for the report and test case.
Verified as described.

regards,
Umesh