Bug #79052 Please add either JSON_ARRAY_SLICE or array range operator
Submitted: 1 Nov 2015 1:53 Modified: 9 Jun 2017 11:26
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:5.7.9 OS:Any
Assigned to: CPU Architecture:Any

[1 Nov 2015 1:53] Roland Bouman
Description:
Working with JSON arrays could be made significantly more convenient if MySQL would have functionality equivalent to javascript Array.slice (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/sli...)

It would not necessarily require adding a new function - it would perhaps be far more powerful to introduce an array range operator for the path syntax. Something like:

mysql> SELECT JSON_EXTRACT('[1,2,3,4,5]', '$[1..3]');
+-------------------------------------+
| JSON_EXTRACT('[1,2,3,4,5]', '$[1..3]') |
+-------------------------------------+
| [2, 3, 4]                     |
+-------------------------------------+
1 row in set (0.00 sec)

where 1..3 means: get elements at indices starting and including 1 up to and including 3.

The advantage of doing this by extending the path syntax is that the array slice operation would this be avaiable to all JSON functions in a consistent way.

How to repeat:
NA

Suggested fix:
Please add array slice operator for json paths, or otherwise add JSON_ARRAY_SLICE function.
[30 Nov 2015 12:41] MySQL Verification Team
Hello Roland,

Thank you for the feature request!

Thanks,
Umesh
[7 Sep 2016 1:45] Karthick Sankarachary
+1!

Hopefully, if and when we get around to addressing this feature request, we will consider supporting the slice operator anywhere in the json path, not just at the top level.
[12 Dec 2016 15:17] Corneliu Maftuleac
+1 PLEASE!!!
[5 Feb 2017 2:18] Istvan Szikra
+1
[9 Jun 2017 11:26] Jon Stephens
Documented as follows in the MySQL 8.0.2 changelog:

    Added support for ranges in the XPath expressions used with many 
    MySQL JSON functions, including JSON_EXTRACT() and JSON_REMOVE(). 
    Such a range is specified using the syntax start to end, where 
    start and end are, respectively, the first and last indexes of a 
    range of elements from a JSON array (always numbered starting with 
    0). For example, $[1 to 3] includes the second, third, and fourth 
    elements, as shown here: 

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
+----------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
+----------------------------------------------+
| [2, 3, 4]                                    |
+----------------------------------------------+
1 row in set (0.00 sec)

    This work also provides support in such expressions for the 
    last keyword, which you can use to represent the index of the 
    last (rightmost) element in the current array, like this: 

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last]');
+--------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last]') |
+--------------------------------------------+
| 5                                          |
+--------------------------------------------+
1 row in set (0.00 sec)

    Indexes relative to the end of the array are also supported, 
    as shown here: 

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-4 to last-2]');
+--------------------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-4 to last-2]') |
+--------------------------------------------------------+
| [1, 2, 3]                                              |
+--------------------------------------------------------+
1 row in set (0.00 sec)

    For further information and examples, see "JSON Path Syntax". 

Closed.