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
82 lines
1.8 KiB
Plaintext
82 lines
1.8 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]'),
|
|
(@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;
|
|
|
|
--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"
|
|
),
|
|
(
|
|
'{"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;
|
|
|
|
DROP TABLE t2;
|
|
|
|
DROP TABLE t1;
|
|
|
|
DROP DATABASE json_extract_db;
|