MDEV-27911: Implement range notation for json path

Range can be thought about in similar manner as wildcard (*) where
more than one elements are processed. To implement range notation, extended
json parser to parse the 'to' keyword and added JSON_PATH_ARRAY_RANGE for
path type. If there is 'to' keyword then use JSON_PATH_ARRAY range for
path type along with existing type.
This new integer to store the end index of range is n_item_end.
When there is 'to' keyword, store the integer in n_item_end else store in
n_item.

1 files changed, 161 insertions, 0 deletions

diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test
index 34a4b30b0ab..2a06313e5c7 100644
--- a/mysql-test/main/func_json.test
+++ b/mysql-test/main/func_json.test
@@ -1316,6 +1316,167 @@ SELECT JSON_VALUE(@json,'$.x[last-0]');
 SELECT JSON_VALUE(@json,'$.x[-0]');
 SELECT JSON_VALUE(@json,'$.x[0]');
 
+
+--echo #
+--echo # MDEV-27911: Implement range notation for json path
+--echo #
+
+SET @json= '[
+             [1, {"key1": "value1"}, 3],
+             [false, 5, 6],
+             [7, 8, [9, {"key2": 2}, 11]],
+             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
+             [19, 20],
+             21, 22
+            ]';
+SELECT JSON_EXISTS(@json, '$[3][3][-2 to last]');
+
+SET @json= '[
+             [1, {"key1": "value1"}, 3],
+             [false, 5, 6],
+             [7, 8, [9, {"key2": 2}, 11]],
+             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
+             [19, 20],
+             21, 22
+            ]';
+SELECT JSON_SEARCH(@json, 'one', '12', NULL, '$[3][0 to 3]');
+
+SET @json= '[
+             [1, {"key1": "value1"}, 3],
+             [false, 5, 6],
+             [7, 8, [9, {"key2": 2}, 11]],
+             [12, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
+             [19, 20]
+            ]';
+SELECT JSON_VALUE(@json, '$[0][1 to 2].key1');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_QUERY(@json, '$.A[-2][-3 to -1]');
+
+SET @json= '[
+             [1, {"key1": "value1"}, 3],
+             [false, 5, 6],
+             [7, 8, [9, {"key2": 2}, 11]],
+             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
+             [19, 20],
+             21, 22
+            ]';
+SELECT JSON_EXTRACT(@json, '$[0 to 3][2]');
+SELECT JSON_EXTRACT(@json, '$[3][3][last-1 to last]');
+SELECT JSON_EXTRACT(@json, '$[3][3][-2 to -1]');
+
+--echo # Checking errors
+
+SET @json= '[
+             [1, {"key1": "value1"}, 3],
+             [false, 5, 6],
+             [7, 8, [9, {"key2": 2}, 11]],
+             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
+             [19, 20],
+             21, 22
+            ]';
+SELECT JSON_CONTAINS_PATH(@json,'one', '$[3][0 to 3]');
+
+SET @json= '[
+             [1, {"key1": "value1"}, 3],
+             [false, 5, 6],
+             [7, 8, [9, {"key2": 2}, 11]],
+             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
+             [19, 20],
+             21, 22
+            ]';
+SELECT JSON_CONTAINS(@json, '$[3][0 to 3]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_ARRAY_INSERT(@json, '$.A[0 to last-1]', 5);
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_ARRAY_APPEND(@json, '$.A[*]', 7);
+
+SET @json= '[
+             [1, {"key1": "value1"}, 3],
+             [false, 5, 6],
+             [7, 8, [9, {"key2": 2}, 11]],
+             [12, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
+             [19, 20]
+            ]';
+SELECT JSON_SET(@json, '$[0][1 to 2].key1', 1);
+
+SET @json= '[
+             [1, {"key1": "value1"}, 3],
+             [false, 5, 6],
+             [7, 8, [9, {"key2": 2}, 11]],
+             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
+             [19, 20],
+             21, 22
+            ]';
+SELECT JSON_REPLACE(@json, '$[1][last-2 to last]', 4);
+
+SET @json= '[
+             [1, {"key1": "value1"}, 3],
+             [false, 5, 6],
+             [7, 8, [9, {"key2": 2}, 11]],
+             [15, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
+             [19, 20],
+             21, 22
+            ]';
+SELECT JSON_REMOVE(@json, '$[1][-6 to last-2]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_KEYS(@json, '$.A[8][1 to 3]');
+
 --echo #
 --echo # End of 10.9 Test
 --echo #