[MDEV-28922] JSON_NORMALIZE handling of duplicate keys Created: 2022-06-21  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.7, 10.8, 10.9
Fix Version/s: 10.11

Type: Bug Priority: Major
Reporter: Wayne Davison (Inactive) Assignee: Vicențiu Ciorbaru
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-16375 Add JSON_NORMALIZE function to normal... Closed

 Description   

There is a bug in the processing of JSON_NORMALIZE() due to the fact that the sort algorithm isn't stable (it doesn't guarantee that duplicates in the input remain in input order). For instance:

SET @j = '{"x": 1, "y": 2, "z": 3, "b": 0, "c": 5, "b":3, "a": 4, "b": 1, "a": 3, "a": 2, "a": 1, "b": 2}';
 
SELECT @k := JSON_NORMALIZE(@j);
 
SELECT JSON_EXTRACT(@j, '$.b'), JSON_EXTRACT(@k, '$.b');

Results in:

+---------------------------------------------------------------------------------------------------------------------------+
 
| @k                                                                                                                        |
 
+---------------------------------------------------------------------------------------------------------------------------+
 
| {"a":4.0E0,"a":1.0E0,"a":2.0E0,"a":3.0E0,"b":1.0E0,"b":2.0E0,"b":3.0E0,"b":0.0E0,"c":5.0E0,"x":1.0E0,"y":2.0E0,"z":3.0E0} |
 
+---------------------------------------------------------------------------------------------------------------------------+
 
+-------------------------+-------------------------+
 
| JSON_EXTRACT(@j, '$.b') | JSON_EXTRACT(@k, '$.b') |
 
+-------------------------+-------------------------+
 
| 0                       | 1.0E0                   |
 
+-------------------------+-------------------------+

Given that duplicate key values are inaccessible via path expressions, I expected JSON_NORMALIZE() to remove duplicate keys so that

{"a": 1, "a": 2}

and

{"a": 1}

would produce the same JSON_NORMALIZE() output (and thus also be JSON_EQUAL()). If this isn't desirable for some reason, the sort must at least be stable so that the extracted values don't jump around.

 Comments   
Comment by Wayne Davison (Inactive) [ 2022-06-21 ]

A case could also be made to have JSON_COMPACT() remove duplicate keys.

Generated at Thu Feb 08 10:04:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.