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)
