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