By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `item` (
`id` INT(11) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`characteristic` JSON NOT NULL
)
INSERT INTO `item` (`id`, `name`, `characteristic`) VALUES
(3, 'Mouse', '[{\"name\": \"length\", \"value\": 6},
{\"name\": \"width\" , \"value\": 3},
{\"name\": \"height\", \"value\": 2}]'),
(4, 'Car' , '[{\"name\": \"length\", \"value\":11},
{\"name\": \"width\" , \"value\": 7},
{\"name\": \"height\", \"value\": 8}]');
WITH RECURSIVE cte AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS value
FROM cte
WHERE cte.n < (SELECT MAX(JSON_LENGTH(`characteristic`)) FROM `item` )
)
SELECT i.*
FROM cte
JOIN `item` AS i
WHERE JSON_EXTRACT(`characteristic`,CONCAT('$[',n-1,'].value')) > 10
AND JSON_EXTRACT(`characteristic`,CONCAT('$[',n-1,'].name')) = "length"
| id | name | characteristic |
|---|---|---|
| 4 | Car | [{"name": "length", "value": 11}, {"name": "width", "value": 7}, {"name": "height", "value": 8}] |
SELECT i.*
FROM `item` AS i
JOIN (SELECT @i := @i + 1 AS n
FROM `item` AS i
JOIN (SELECT @i := -1) AS iter
LEFT JOIN information_schema.tables AS t
ON @i < JSON_LENGTH(`characteristic`) - 1) AS t
WHERE JSON_EXTRACT(`characteristic`,CONCAT('$[',n,'].value')) > 10
AND JSON_EXTRACT(`characteristic`,CONCAT('$[',n,'].name')) = "length"
| id | name | characteristic |
|---|---|---|
| 4 | Car | [{"name": "length", "value": 11}, {"name": "width", "value": 7}, {"name": "height", "value": 8}] |