-- Table with CLOB for JSON
DROP TABLE test.json_table;
CREATE TABLE test.json_table(id INTEGER NOT NULL, text CLOB(1 M) INLINE LENGTH 4000);
-- Execute this block 5 times to insert 100,000 "fred" rows (CLP: db2 -td@)
BEGIN ATOMIC
DECLARE i INT DEFAULT 0;
WHILE i < 20000 DO
INSERT INTO test.json_table(id, text)
VALUES
(NEXT VALUE FOR test.id, '{"padding": "' || REPEAT('?', 3900) || '", "name": "fred"}');
SET i = i + 1;
END WHILE;
END@
-- Insert 1 "freda" row
INSERT INTO test.json_table(id, text)
VALUES
(NEXT VALUE FOR test.id, '{"padding": "' || REPEAT('?', 30) || '", "name": "freda"}');
-- Test SQL
VALUES CURRENT_TIMESTAMP;
SELECT
name,
COUNT(*) AS count
FROM
(
SELECT
JSON_VALUE(text, 'lax $.name' RETURNING VARCHAR(30)) AS name
FROM
test.json_table
)
GROUP BY
name;
VALUES CURRENT_TIMESTAMP;
-- Add index after first test to speed performance.
CREATE INDEX test.ix01 ON test.json_table(JSON_VALUE(text, 'lax $.name' RETURNING VARCHAR(30)));
-- Results (no index)
VALUES CURRENT_TIMESTAMP
1
--------------------------
2022-12-16-13.49.48.970190
1 record(s) selected.
SELECT name, COUNT(*) AS count FROM ( SELECT JSON_VALUE(text, 'lax $.name' RETURNING VARCHAR(30)) AS name FROM test.json_table ) GROUP BY name
NAME COUNT
------------------------------ -----------
fred 100000
freda 1
2 record(s) selected.
VALUES CURRENT_TIMESTAMP
1
--------------------------
2022-12-16-13.49.50.879429
1 record(s) selected.
-- Results (with index)
VALUES CURRENT_TIMESTAMP
1
--------------------------
2022-12-16-13.51.54.364794
1 record(s) selected.
SELECT name, COUNT(*) AS count FROM ( SELECT JSON_VALUE(text, 'lax $.name' RETURNING VARCHAR(30)) AS name FROM test.json_table ) GROUP BY name
NAME COUNT
------------------------------ -----------
fred 100000
freda 1
2 record(s) selected.
VALUES CURRENT_TIMESTAMP
1
--------------------------
2022-12-16-13.51.54.372007
1 record(s) selected.