[MDEV-24259] JSON_EXTRACT returns wrong result with ** Created: 2020-11-20  Updated: 2023-04-27

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

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None


 Description    

set @json_doc3 =
 
'
 
{
 
  "root": {
 
     "child1" : {
 
       "child2" : {
 
         "child1" : {
 
           "x":124
 
          }
 
        }
 
      }
 
   }
 
}
 
';


MariaDB [test]> select json_extract(@json_doc3, '$**.child1**.x');
 
+--------------------------------------------+
 
| json_extract(@json_doc3, '$**.child1**.x') |
 
+--------------------------------------------+
 
| NULL                                       |
 
+--------------------------------------------+

This should not return NULL.

One can debate whether this should return the value of x once (because the document has just one x node) or twice (because there are two ways to match the pattern).

 Comments   
Comment by Sergei Petrunia [ 2020-11-20 ]

MySQL returns this:

MySQL-8> select json_extract(@json_doc3, '$**.child1**.x');
 
+--------------------------------------------+
 
| json_extract(@json_doc3, '$**.child1**.x') |
 
+--------------------------------------------+
 
| [124]                                      |
 
+--------------------------------------------+

The reason we're looking at MySQL here is that the double-asterisk pattern, ** is not part of the standard, it has appeared in MySQL first

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