You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-11-03 17:13:17 +03:00
9fe37d5919
This patch is the columnstore-part of the task. Columnstore wanted to have previous 32 depth, so this patch aims at keeping the compatibility.
74 lines
2.5 KiB
Plaintext
74 lines
2.5 KiB
Plaintext
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]');
|
|
INSERT INTO t1 VALUES (@json, '$[1]');
|
|
INSERT INTO t1 VALUES (@json, '$[2]');
|
|
INSERT INTO t1 VALUES (@json, '$[3]');
|
|
INSERT INTO t1 VALUES (@json, '$[2][0]');
|
|
INSERT INTO t1 VALUES (@json, '$[2][1]');
|
|
INSERT INTO t1 VALUES (@json, '$[2][10]');
|
|
INSERT INTO t1 VALUES (@json, '$');
|
|
INSERT INTO t1 VALUES ('1', '$');
|
|
INSERT INTO t1 VALUES ('[10, 20, [30, 40], 1, 10]', '$[1]');
|
|
INSERT INTO t1 VALUES ('{"key1":"asd", "key2":[2,3]}', "$.key1");
|
|
INSERT INTO t1 VALUES ('{"key0":true, "key1":"qwe"}', "$.key1");
|
|
INSERT INTO t1 VALUES ('[10, 20, [30, 40]]', '$[2][*]');
|
|
INSERT INTO t1 VALUES ('[10, 20, [{"a":3}, 30, 40]]', '$[2][*]');
|
|
INSERT INTO t1 VALUES (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");
|
|
INSERT INTO t2 VALUES ('{"key1":"asd", "key2":[2,3]}', "$.key1", "$.key2");
|
|
INSERT INTO t2 VALUES ('{"key1":5, "key2":[2,3]}', "$.key1", "$.key2");
|
|
INSERT INTO t2 VALUES ('[10, 20, [30, 40], 1, 10]', '$[1]', '$[25]');
|
|
INSERT INTO t2 VALUES ('[{"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;
|