[MDEV-24631] JSON_EXTRACT returns result in document and not in path order Created: 2021-01-20  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.5.5, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Johan Wikman Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Consider the following:

> SELECT JSON_EXTRACT('{ "a":1, "b":2}', '$.b', '$.a');
 
+-----------------------------------------------+
 
| JSON_EXTRACT('{ "a":1, "b":2}', '$.b', '$.a') |
 
+-----------------------------------------------+
 
| [1, 2]                                        |
 
+-----------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [(none)]> SELECT JSON_EXTRACT('{ "a":1, "b":2}', '$.a', '$.b');
 
+-----------------------------------------------+
 
| JSON_EXTRACT('{ "a":1, "b":2}', '$.a', '$.b') |
 
+-----------------------------------------------+
 
| [1, 2]                                        |
 
+-----------------------------------------------+
 
1 row in set (0.000 sec)

The selected field values are returned in the order in which they appear in the document, and not in the order of the path arguments given to JSON_EXTRACT.

The documentation at https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-extract states If it is possible that those arguments could return multiple values, the matched values are autowrapped as an array, in the order corresponding to the paths that produced them.

Indeed, unless that is done, multiple path arguments cannot be used, as you will not know which returned value corresponds to which path.

 Comments   
Comment by Alice Sherepa [ 2021-01-20 ]

Tnanks for the report! Reproducible on 10.2-10.5

MariaDB [test]> SELECT JSON_EXTRACT('{ "a":1, "b":2, "c":[3,4]}', '$.c', '$.b');
 
+----------------------------------------------------------+
 
| JSON_EXTRACT('{ "a":1, "b":2, "c":[3,4]}', '$.c', '$.b') |
 
+----------------------------------------------------------+
 
| [2, [3, 4]]                                              |
 
+----------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> SELECT JSON_EXTRACT('{ "a":1, "b":2, "c":[3,4]}', '$.c', '$.c');
 
+----------------------------------------------------------+
 
| JSON_EXTRACT('{ "a":1, "b":2, "c":[3,4]}', '$.c', '$.c') |
 
+----------------------------------------------------------+
 
| [[3, 4]]                                                 |
 
+----------------------------------------------------------+
 
1 row in set (0.000 sec)
 
#expected [[3, 4], [3, 4]]

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