[MDEV-20294] SQL/JSON path doesn't unwrap Created: 2019-08-08  Updated: 2022-06-01

Status: Confirmed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.4.7, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Markus Winand Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None


 Description   

SQL/JSON path lax mode (default) requires unwrapping arrays in several cases, out of which MariaDB only supports the member accessor and wildcard member accessor (SQL-2:2016 3.39 General Rule 11 g 1 B I and 2 A I).

MariaDB doesn't do that.

MariaDB 5.5.5-10.4.7-MariaDB> select json_value('{"a": [{"b": 9}]}', '$.a.b');
 
+------------------------------------------+
 
| json_value('{"a": [{"b": 9}]}', '$.a.b') |
 
+------------------------------------------+
 
| NULL                                     |
 
+------------------------------------------+
 
1 row in set (0.00 sec)

This should basically work like this:

MariaDB 5.5.5-10.4.7-MariaDB> select json_value('{"a": [{"b": 9}]}', '$.a[*].b');
 
+---------------------------------------------+
 
| json_value('{"a": [{"b": 9}]}', '$.a[*].b') |
 
+---------------------------------------------+
 
| 9                                           |
 
+---------------------------------------------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Alice Sherepa [ 2019-08-12 ]

JSON_VALUE returns a scalar, JSON_QUERY returns an object or an array. It does not look so neat, but:

MariaDB [test]> select json_value(json_query('{"a": [{"b": 9}]}', '$.a[0]'),'$.b');
 
+-------------------------------------------------------------+
 
| json_value(json_query('{"a": [{"b": 9}]}', '$.a[0]'),'$.b') |
 
+-------------------------------------------------------------+
 
| 9                                                           |
 
+-------------------------------------------------------------+
 
1 row in set (0.00 sec)

Comment by Markus Winand [ 2019-08-13 ]

The point of unwrap in SQL/JSON path lax mode is to cope with JSON whos structure may vary in such a way that some documents contains an array with (possibly) multiple entries, but those that have only a single entry might lack the array.

e.g. The SQL/JSON path expression '$.a.b' should return 9 for both of these documents:

{"a": [{"b": 9}]}


{"a":  {"b": 9} }

Comment by Elena Stepanova [ 2020-04-28 ]

Here is an example of the same issue as reported, but this one is directly from the standard:

drop table if exists T;
 
create table T (K int, J json);
 
insert into T values
 
    (103,'{ "who": "Jack","friends": [ { "name": "Connie" } ] }');
 
SELECT JSON_VALUE (T.J, '$.friends.name') AS Friend FROM T;

According to the standard, it should return the value Connie. But it returns NULL instead:

10.4 b6344698

 
MariaDB [test]> create table T (K int, J json);
 
Query OK, 0 rows affected (0.657 sec)
 
 
 
MariaDB [test]> insert into T values
 
    ->     (103,'{ "who": "Jack","friends": [ { "name": "Connie" } ] }');
 
Query OK, 1 row affected (0.217 sec)
 
 
 
MariaDB [test]> SELECT JSON_VALUE (T.J, '$.friends.name') AS Friend FROM T;
 
+--------+
 
| Friend |
 
+--------+
 
| NULL   |
 
+--------+
 
1 row in set (0.012 sec)

Generated at Thu Feb 08 08:58:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.