DROP DATABASE IF EXISTS json_extract_db;
CREATE DATABASE json_extract_db;
USE json_extract_db;
# ----------------------------------------------------------------------
# Test of JSON_EXTRACT function.
# ----------------------------------------------------------------------

# Single path expression

CREATE TABLE t1(j LONGTEXT, p LONGTEXT) ENGINE = COLUMNSTORE;
SET
@json = '[1, "val2", [3.1, -4]]';
INSERT INTO
t1
VALUES
(@json, '$[0]'),
(@json, '$[1]'),
(@json, '$[2]'),
(@json, '$[3]'),
(@json, '$[2][0]'),
(@json, '$[2][1]'),
(@json, '$[2][10]'),
(@json, '$'),
('1', '$'),
('[10, 20, [30, 40], 1, 10]', '$[1]'),
('{"key1":"asd", "key2":[2,3]}', "$.key1"),
('{"key0":true, "key1":"qwe"}', "$.key1"),
('[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
t1;
j	p	result
[1, "val2", [3.1, -4]]	$[0]	1
[1, "val2", [3.1, -4]]	$[1]	"val2"
[1, "val2", [3.1, -4]]	$[2]	[3.1, -4]
[1, "val2", [3.1, -4]]	$[3]	NULL
[1, "val2", [3.1, -4]]	$[2][0]	3.1
[1, "val2", [3.1, -4]]	$[2][1]	-4
[1, "val2", [3.1, -4]]	$[2][10]	NULL
[1, "val2", [3.1, -4]]	$	[1, "val2", [3.1, -4]]
1	$	1
[10, 20, [30, 40], 1, 10]	$[1]	20
{"key1":"asd", "key2":[2,3]}	$.key1	"asd"
{"key0":true, "key1":"qwe"}	$.key1	"qwe"
[10, 20, [30, 40]]	$[2][*]	[30, 40]
[10, 20, [{"a":3}, 30, 40]]	$[2][*]	[{"a": 3}, 30, 40]
{"foo": "foobar"}	$	{"foo": "foobar"}

# Multiple path expression

CREATE TABLE t2(j LONGTEXT, p1 LONGTEXT, p2 LONGTEXT) ENGINE = COLUMNSTORE;
INSERT INTO
t2
VALUES
(
'{"key1":"asd", "key2":[2,3]}',
"$.keyX",
"$.keyY"
      ),
(
'{"key1":"asd", "key2":[2,3]}',
"$.key1",
"$.key2"
      ),
('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2"),
('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]'),
('[{"a": [3, 4]}, {"b": 2}]', '$[0].a', '$[1].a');
SELECT
j,
p1,
p2,
JSON_EXTRACT(j, p1, p2) AS result
FROM
t2;
j	p1	p2	result
{"key1":"asd", "key2":[2,3]}	$.keyX	$.keyY	NULL
{"key1":"asd", "key2":[2,3]}	$.key1	$.key2	["asd", [2, 3]]
{"key1":5, "key2":[2,3]}	$.key1	$.key2	[5, [2, 3]]
[10, 20, [30, 40], 1, 10]	$[1]	$[25]	[20]
[{"a": [3, 4]}, {"b": 2}]	$[0].a	$[1].a	[[3, 4]]
DROP TABLE t2;
DROP TABLE t1;
DROP DATABASE json_extract_db;