[MDEV-24573] JSON Path evaluation is not standard-compliant Created: 2021-01-11  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: upstream-8.0


 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"]                        |
 
+-----------------------------------------+



 Comments   
Comment by Sergei Petrunia [ 2021-01-11 ]

PostgreSQL 13.0 works according to the standard:

test=# select jsonb_path_query('
 
test'# { "phones": [ { "type": "cell", "number": "abc-defg" },
 
test'#             { "number": "pqr-wxyz" },
 
test'#             { "type": "home", "number": "hij-klmn" } 
test'#           ] 
test'# }',
 
test(# '$.phones.type');
 
 jsonb_path_query 
------------------
 
 "cell"
 
 "home"
 
(2 rows)

Comment by Sergei Petrunia [ 2021-01-11 ]

MySQL 8.0.22-13 (I am using Percona Server but I think there's no difference) also produces wrong result:

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)

Comment by Sergei Petrunia [ 2021-01-11 ]

MS SQL Server 2019: produces NULL value (like MariaDB and MySQL)

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a75b0e6a08e666102fdbd9cb8678b963

select json_value('
 
{ "phones": [ { "type": "cell", "number": "abc-defg" },
 
            { "number": "pqr-wxyz" },
 
            { "type": "home", "number": "hij-klmn" } 
          ] 
}',
 
'$.phones.type');

Comment by Sergei Petrunia [ 2021-01-12 ]

Filed http://bugs.mysql.com/102233 against MySQL.

Generated at Thu Feb 08 09:31:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.