mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-20 09:07:44 +03:00
cdd41f05f3
The following functions are created: Create function JSON_VALID and test cases Create function JSON_DEPTH and test cases Create function JSON_LENGTH and test cases Create function JSON_EQUALS and test cases Create function JSON_NORMALIZE and test cases Create function JSON_TYPE and test cases Create function JSON_OBJECT and test cases Create function JSON_ARRAY and test cases Create function JSON_KEYS and test cases Create function JSON_EXISTS and test cases Create function JSON_QUOTE/JSON_UNQUOTE and test cases Create function JSON_COMPACT/DETAILED/LOOSE and test cases Create function JSON_MERGE and test cases Create function JSON_MERGE_PATCH and test cases Create function JSON_VALUE and test cases Create function JSON_QUERY and test cases Create function JSON_CONTAINS and test cases Create function JSON_ARRAY_APPEND and test cases Create function JSON_ARRAY_INSERT and test cases Create function JSON_INSERT/REPLACE/SET and test cases Create function JSON_REMOVE and test cases Create function JSON_CONTAINS_PATH and test cases Create function JSON_OVERLAPS and test cases Create function JSON_EXTRACT and test cases Create function JSON_SEARCH and test cases Note: Some functions output differs from MDB because session variables that affects functions output,e.g JSON_QUOTE/JSON_UNQUOTE This depends on MCOL-5212
89 lines
2.2 KiB
Plaintext
89 lines
2.2 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]'),
|
|
(@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;
|