MariaDB/MySQL -如何根据嵌套对象的键查询JSON

Question

假设以下JSON对象存储在MariaDB/MySQL中：

SET @j = '{
    "thing": {
        "sub_things": [
            {
                "attribute": [
                    { "1": 40 },
                    { "5": 25 },
                    { "13": 35 }
                ]
            },
            {
                "attribute": [
                    { "2": 50 },
                    { "7": 50 }
                ]
            }
        ]
    }
}'

当attribute数组中的一个对象有一个特定的键时，我如何查询它来返回sub_things，例如，当键是13时，它应该返回第一个sub_thing。

谢谢!

Answer 1

我不确定在单个查询中是否可以实现所需的内容。

下面的存储过程可能会给您一些启发(存储过程适用于MariaDB和MySQL)：

> DROP PROCEDURE IF EXISTS `JSON_query_based_on_key`;
Query OK, 0 rows affected (0.01 sec)

> DELIMITER //

> CREATE PROCEDURE `JSON_query_based_on_key`(
->   `json` TEXT,
->   `key` VARCHAR(5)
-> )
-> BEGIN
->   DECLARE `sub_things_current` INT
->      DEFAULT JSON_LENGTH(`json`, '$.thing.sub_things') - 1;
-> 
->   WHILE (`sub_things_current` > -1) DO
->     IF NOT JSON_CONTAINS_PATH(
->       `json`, 
->       'one', 
->       CONCAT('$.thing.sub_things[', `sub_things_current`, '].attribute[*]."', `key`, '"')
->     ) THEN
->       SET `json` := JSON_REMOVE(
->         `json`,
->         CONCAT('$.thing.sub_things[', `sub_things_current`, ']'));
->     END IF;
->     SET `sub_things_current` := `sub_things_current` - 1;
->   END WHILE;
-> 
->   SELECT JSON_EXTRACT(`json`, '$.thing');
-> END//
Query OK, 0 rows affected (0.00 sec)

> DELIMITER ;

> CALL `JSON_query_based_on_key`('{
'>   "thing": {
'>     "sub_things": [
'>       {
'>         "attribute": [
'>           { "1": 40 },
'>           { "5": 25 },
'>           { "13": 35 }
'>         ]
'>       },
'>       {
'>         "attribute": [
'>           { "2": 50 },
'>           { "7": 50 }
'>         ]
'>       }
'>     ]
'>   }
'> }', '13');
+---------------------------------------------------------------------+
| JSON_EXTRACT(`json`, '$.thing')                                     |
+---------------------------------------------------------------------+
| {"sub_things": [{"attribute": [{"1": 40}, {"5": 25}, {"13": 35}]}]} |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

根据需要修改代码。

请参阅用于MariaDB的db<>fiddle (10.2.6)和用于MySQL的db-fiddle (5.7.17)。