1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
SET @@cte_max_recursion_depth := 10000;
DROP TABLE IF EXISTS test;
CREATE TABLE test (id INT, json_value JSON);
# An array of 10 elements with pseudo-random values in each row
INSERT INTO test (id, json_value)
WITH RECURSIVE cte AS (
SELECT 1 id
UNION ALL
SELECT 1 + id FROM cte WHERE id < @@cte_max_recursion_depth
)
SELECT id,
JSON_ARRAY(CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED))
FROM cte
WHERE (@seed := 0) = 0
ORDER BY id;
SELECT id, CAST(json_value AS CHAR) json_value FROM test ORDER BY id LIMIT 20;
# Set a variable to a value found in the row with id=10
SET @random_value := 37947;
# Select id of rows in which this value is present
# 5 rows will be found, with id values of 10, 2486, 4962, 7438, 9915
SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$');
ALTER TABLE test ADD INDEX mvi ((CAST(json_value->'$' AS UNSIGNED ARRAY)));
EXPLAIN SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$');
SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$');
# Look at the rows 1311 and 1312 (the values are found experimentally)
SELECT id, CAST(json_value AS CHAR) json_value
FROM test
WHERE id IN (1311, 1312);
SELECT id FROM test WHERE 48176 MEMBER OF (json_value->'$');
SELECT id FROM test WHERE 74452 MEMBER OF (json_value->'$');
DROP TABLE test;
|
