You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-10-31 18:30:33 +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.
68 lines
1.9 KiB
Plaintext
68 lines
1.9 KiB
Plaintext
--source ../include/have_columnstore.inc
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS json_extract_db;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE json_extract_db;
|
|
|
|
USE json_extract_db;
|
|
|
|
--echo # ----------------------------------------------------------------------
|
|
--echo # Test of JSON_EXTRACT function.
|
|
--echo # ----------------------------------------------------------------------
|
|
|
|
--echo
|
|
--echo # Single path expression
|
|
--echo
|
|
|
|
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;
|
|
|
|
--echo
|
|
--echo # Multiple path expression
|
|
--echo
|
|
|
|
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;
|
|
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
DROP DATABASE json_extract_db;
|