Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Description
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.zip
open it at page 70 (section 6.10.1 Member accessor), and read:
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 ...
and then Table 35 shows that the result is "cell", "home".
The "context item" here obviously misses quotes around names of the members. I added them and I try the example on MariaDB 10.5.9:
MariaDB [test]> set @json='
|
{ "phones": [ { "type": "cell", "number": "abc-defg" },
|
{ "number": "pqr-wxyz" },
|
{ "type": "home", "number": "hij-klmn" }
|
]
|
}';
|
Query OK, 0 rows affected (0.000 sec)
|
 |
MariaDB [test]> select json_extract(@json, '$.phones.type');
|
+--------------------------------------+
|
| json_extract(@json, '$.phones.type') |
|
+--------------------------------------+
|
| NULL |
|
+--------------------------------------+
|
1 row in set (0.000 sec)
|
If I add an array accessor step (which makes array unwrapping unnecessary), I get the expected result:
MariaDB [test]> select json_extract(@json, '$.phones[*].type');
|
+-----------------------------------------+
|
| json_extract(@json, '$.phones[*].type') |
|
+-----------------------------------------+
|
| ["cell", "home"] |
|
+-----------------------------------------+
|