[MDEV-21891] json_search returns incorrect result when keys contain dots Created: 2020-03-08  Updated: 2023-04-27

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

Type: Bug Priority: Major
Reporter: Moshe Dolev Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None


 Description   

The query:

SELECT json_search('{"19.50":{"20.50":[1,{"a":6}]}}','one',6)

returns this:

"$.19.50.20.50[1].a"

But when you try:

SELECT json_value('{"19.50":{"20.50":[1,{"a":6}]}}',"$.19.50.20.50[1].a")

you get NULL.

Correct answer for the json_search query, would be:

'$."19.50"."20.50"[1]."a"'

Tested on 10.2.22 OS: centos 7

 Comments   
Comment by Elena Stepanova [ 2020-04-05 ]

I am not sure whether it should return a value according to (often vague) JSON standards, but at least the behavior is clearly inconsistent. MySQL 5.7, in comparison, already returns NULL on json_search, and complains about an invalid path on json_extract.

MySQL 5.7.25

 
SELECT   json_search('{"1950":{"2050":[1,{"a":6}]}}','one',6);
 
json_search('{"1950":{"2050":[1,{"a":6}]}}','one',6)
 
NULL
 
 
 
SELECT  json_extract('{"19.50":{"20.50":[1,{"a":6}]}}',"$.19.50.20.50[1].a")' failed: 3143: Invalid JSON path expression. The error is around character position 4

MariaDB 10.2 cd88a606

 
SELECT   json_search('{"19.50":{"20.50":[1,{"a":6}]}}','one',6);
 
json_search('{"19.50":{"20.50":[1,{"a":6}]}}','one',6)
 
"$.19.50.20.50[1].a"
 
 
 
SELECT  json_extract('{"19.50":{"20.50":[1,{"a":6}]}}',"$.19.50.20.50[1].a");
 
json_extract('{"19.50":{"20.50":[1,{"a":6}]}}',"$.19.50.20.50[1].a")
 
NULL
 
SELECT    json_value('{"19.50":{"20.50":[1,{"a":6}]}}',"$.19.50.20.50[1].a");
 
json_value('{"19.50":{"20.50":[1,{"a":6}]}}',"$.19.50.20.50[1].a")
 
NULL

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