[MDEV-15322] JSON_VALUE and JSON_QUERY don't handle wildcards in path Created: 2018-02-15  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-12131 JSON path as used by MariaDB 10.2 JSO... Open

 Description   

_Note: it might be a documentation issue, please then documented it properly. For now, I don't see anything that would suggest this difference in behavior between JSON_VALUE / JSON_QUERY and JSON_EXTRACT

MariaDB [test]> SELECT JSON_EXTRACT('{"foo":{"bar":"qux"}}','$**.bar');
 
+-------------------------------------------------+
 
| JSON_EXTRACT('{"foo":{"bar":"qux"}}','$**.bar') |
 
+-------------------------------------------------+
 
| ["qux"]                                         |
 
+-------------------------------------------------+
 
1 row in set (0.00 sec)


MariaDB [test]> SELECT JSON_QUERY('{"foo":{"bar":"qux"}}','$**.bar');
 
+-----------------------------------------------+
 
| JSON_QUERY('{"foo":{"bar":"qux"}}','$**.bar') |
 
+-----------------------------------------------+
 
| NULL                                          |
 
+-----------------------------------------------+
 
1 row in set (0.00 sec)


MariaDB [test]> SELECT JSON_VALUE('{"foo":{"bar":"qux"}}','$**.bar');
 
+-----------------------------------------------+
 
| JSON_VALUE('{"foo":{"bar":"qux"}}','$**.bar') |
 
+-----------------------------------------------+
 
| NULL                                          |
 
+-----------------------------------------------+
 
1 row in set (0.01 sec)

Given the current description of JSON_VALUE and JSON_QUERY, I would expect one of them to return a non-null value.

 Comments   
Comment by Nayuta Yanagisawa (Inactive) [ 2022-06-01 ]

elenst Is this 10.2 only bug? If so, we can close it because we 10.2 is no longer supported.

Comment by Elena Stepanova [ 2022-06-01 ]

No, of course it's not 10.2-only bug, it was just filed when only 10.2 was mature enough to mention as affected.

I've updated the versions.

Also, since then additional information appeared from MySQL side. MySQL 5.7 didn't have either JSON_VALUE or JSON_QUERY, so the comparison wasn't possible, but MySQL 8.0 does have JSON_VALUE:

MySQL 8.0.28

 
MySQL [test]> SELECT JSON_VALUE('{"foo":{"bar":"qux"}}','$**.bar');
 
+-----------------------------------------------+
 
| JSON_VALUE('{"foo":{"bar":"qux"}}','$**.bar') |
 
+-----------------------------------------------+
 
| qux                                           |
 
+-----------------------------------------------+
 
1 row in set (0.013 sec)
 
 
 
MySQL [test]> SELECT JSON_QUERY('{"foo":{"bar":"qux"}}','$**.bar');
 
ERROR 1305 (42000): FUNCTION test.JSON_QUERY does not exist
 
MySQL [test]> SELECT JSON_EXTRACT('{"foo":{"bar":"qux"}}','$**.bar');
 
+-------------------------------------------------+
 
| JSON_EXTRACT('{"foo":{"bar":"qux"}}','$**.bar') |
 
+-------------------------------------------------+
 
| ["qux"]                                         |
 
+-------------------------------------------------+
 
1 row in set (0.000 sec)

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