Search in Mysql Json Collection data

user_14846563 0
Viewed 92

I've a problem and I wish get your help to solve it.

Actually, I've an items Mysql table. And I have a characteristics column which is json datatype.

This is an example to explain what I'm saying.

  • The table structure
CREATE TABLE item ( 
                    `id`             INT(11)     NOT NULL, 
                    `name`           VARCHAR(20) NOT NULL, 
                    `characteristic` JSON        NOT NULL 
                   )   
       ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • The table data
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}]');

So, I need to get all items which have the characteristic length > 10.

Many thanks in advance for your suggestions and help.

mysql-5.7 mysql arrays sql json
1 Answers

You can use JSON_EXTRACT() function along with Recursive CTE such as

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"

Considering your DB version is 5.7, you can use the following iteration logic

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"

in this case the index of the key "name" equals to "length" is determined within the array, and then this index is used to check out whether the "value" is greater than 10.

Demo

user_58413060
Related