DROP DATABASE IF EXISTS json_range_expr_db;
CREATE DATABASE json_range_expr_db;
USE json_range_expr_db;
# Range expression is supported in MariaDB 10.9 binaries
CREATE TABLE t2(j TEXT, p TEXT) ENGINE = columnstore;
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
           }';
INSERT INTO
t2
VALUES
(@json, '$.A[-2][-1]'),
(@json, '$.A[last-1][last]');
SELECT
j,
p,
JSON_EXISTS(j, p) AS result
FROM
t2;
j	p	result
{
            "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
           }	$.A[-2][-1]	1
{
            "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
           }	$.A[last-1][last]	1
# Test case 2
CREATE TABLE t3(j TEXT, p TEXT) ENGINE = columnstore;
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
            ]';
INSERT INTO
t3
VALUES
(@json, '$[3][3][-2 to last]');
SELECT
j,
p,
JSON_EXISTS(j, p) AS result
FROM
t3;
j	p	result
[
             [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
            ]	$[3][3][-2 to last]	1
# Test case 3
CREATE TABLE t4(j TEXT, p TEXT) ENGINE = columnstore;
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
            ]';
INSERT INTO
t4
VALUES
(@json, '$[2][2][1 to 2]'),
(@json, '$[2][2][4 to 6]'),
(@json, '$[2][2][1 to 4]');
SELECT
j,
p,
JSON_EXISTS(j, p) AS result
FROM
t4;
j	p	result
[
             [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
            ]	$[2][2][1 to 2]	1
[
             [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
            ]	$[2][2][4 to 6]	0
[
             [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
            ]	$[2][2][1 to 4]	1

# JSON_EXTRACT

CREATE TABLE t5(j LONGTEXT, p LONGTEXT) ENGINE = COLUMNSTORE;
INSERT INTO
t5
VALUES
('[1, "val2", [3.1, -4]]', '$'),
('1', '$'),
('[10, 20, [30, 40]]', '$[2][*]'),
('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]'),
(json_object('foo', 'foobar'), '$');
SELECT
j,
p,
JSON_EXTRACT(j, p) AS result
FROM
t5;
j	p	result
[1, "val2", [3.1, -4]]	$	[1, "val2", [3.1, -4]]
1	$	1
[10, 20, [30, 40]]	$[2][*]	[30, 40]
[10, 20, [{"a":3}, 30, 40]]	$[2][*]	[{"a": 3}, 30, 40]
{"foo": "foobar"}	$	{"foo": "foobar"}

# JSON_EXTRACT

CREATE TABLE t6(j TEXT, f TEXT, v TEXT, e TEXT, p TEXT) ENGINE = COLUMNSTORE;
SET
@j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
INSERT INTO
t6
VALUES
(@j, 'all', 'abc', NULL, '$'),
(@j, 'all', '10', NULL, '$'),
(@j, 'all', '10', NULL, '$[*]'),
(@j, 'all', '10', NULL, '$[*][0].k'),
(@j, 'all', '10', NULL, '$**.k');
SELECT
j AS json,
f AS return_arg,
v AS search_str,
e AS escape_char,
p AS path,
JSON_SEARCH(j, f, v, NULL, p) AS result
FROM
t6;
json	return_arg	search_str	escape_char	path	result
["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]	all	abc	NULL	$	["$[0]", "$[2].x"]
["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]	all	10	NULL	$	"$[1][0].k"
["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]	all	10	NULL	$[*]	"$[1][0].k"
["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]	all	10	NULL	$[*][0].k	"$[1][0].k"
["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]	all	10	NULL	$**.k	"$[1][0].k"
DROP TABLE t6;
DROP TABLE t5;
DROP TABLE t4;
DROP TABLE t3;
DROP TABLE t2;
DROP DATABASE json_range_expr_db;