[MDEV-22224] Support JSON Path negative index Created: 2020-04-12  Updated: 2023-08-07  Resolved: 2022-04-13

Status: Closed
Project: MariaDB Server
Component/s: JSON
Fix Version/s: 10.9.1

Type: Task Priority: Critical
Reporter: Federico Razzoli Assignee: Rucha Deodhar
Resolution: Fixed Votes: 1
Labels: Preview_10.9, json_path

Issue Links:
PartOf
is part of MDEV-28112 prepare 10.9.0 preview releases Closed
Problem/Incident
causes MDEV-28319 Assertion `cur_step->type & JSON_PATH... Closed
causes MDEV-29381 JSON paths containing dashes are repo... Closed
causes MDEV-29586 JSON_VALUE and JSON_EXTRACT doesn't h... Closed
Relates
relates to MDEV-27972 Unexpected behavior with negative zer... Closed
relates to MDEV-28350 Test failing on buildbot with UBSAN Closed

 Description   

JSON Path allows to specify a negative array index, eg $[-1] returns the last element. This is not supported in MariaDB.

Because of this, I believe there is no way to retrieve the last element without knowing the array length, nor to insert (JSON_ARRAY_INSERT()) an element at the end of the array. If I'm correct, this is a major missing feature for those who use JSON functions.

MariaDB [test]> SELECT JSON_EXTRACT(JSON_ARRAY(1, 2, 3), '$[-1]');
 
+--------------------------------------------+
 
| JSON_EXTRACT(JSON_ARRAY(1, 2, 3), '$[-1]') |
 
+--------------------------------------------+
 
| NULL                                       |
 
+--------------------------------------------+
 
1 row in set, 1 warning (0.000 sec)
 
 
 
Warning (Code 4042): Syntax error in JSON path in argument 2 to function 'json_extract' at position 3

UPDATE: The task includes adding the negative index and the new keyword "last"

 Comments   
Comment by Rucha Deodhar [ 2021-12-04 ]

Patch:
https://github.com/MariaDB/server/commit/e093379538dca9a59a20ec830eee868d3f0ddcc7

Comment by Alexey Botchkov [ 2022-01-27 ]

ok to push.

Comment by Rucha Deodhar [ 2022-02-15 ]

Pushed to preview branch: https://github.com/MariaDB/server/tree/preview-10.9-MDEV-22224
b4a92e2

Comment by Sergei Petrunia [ 2022-02-18 ]

MySQL also supports last as a way to indicate the last array element. See
https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-syntax . last is not mentioned in the BNF forms, but at the bottom of the page they have:

MySQL 8.0 also supports range notation for subsets of JSON arrays using the to keyword (such as $[2 to 10]), as well as the last keyword as a synonym for the rightmost element of an array. See Searching and Modifying JSON Values, for more information and examples.

AFAIU, [last] is also in the SQL standard (grep for <JSON last subscript> in the standard text.

holyfoot, rucha174 - what do you think of implementing [last] ? It's probably out of scope of this MDEV, but it should be fairly easy to do now?

Comment by Rucha Deodhar [ 2022-02-18 ]

psergei Yes, I agree. Implementing [last] should be fairly easy to do. Thanks for bringing it to notice. I'll assign the task to myself.

Comment by Rucha Deodhar [ 2022-02-19 ]

holyfoot : Patch for implementing [last-n] : https://github.com/MariaDB/server/commit/97d3657710086ada1264fdf4d8cca58db6991be4

Comment by Alexey Botchkov [ 2022-02-28 ]

Patch modification was discussed on slack.

Comment by Alexey Botchkov [ 2022-02-28 ]

ok to push

Comment by Lena Startseva [ 2022-03-02 ]

Testing done

Comment by Steven Haigh [ 2022-11-30 ]

I'm trying to track back a regression that MIGHT end up being related to this.

Given the following query:

SELECT JSON_EXTRACT('{ "temperatures": { "AO-therm": "value" } }', '$.temperatures.AO-therm');

The results for different versions of MariaDB are as follows:

  • 10.11-rc --> null
  • 10.10.4 --> null
  • 10.9.4 --> null
  • 10.8.6 --> "value"

As such, this seems like a regression in behaviours from 10.8.6 onwards.

Comment by Sergei Golubchik [ 2022-12-01 ]

Yes, it is. See "causes" links above.

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