MariaDB/MySQL - How to query JSON based on key of nested object

Question

Given the following JSON object stored in MariaDB/MySQL:

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

How would I query this to return sub_things where one of the objects in the attribute array has a particular key e.g. where the key is 13 this should return the first sub_thing.

Thanks!

While it is possible, hiding things that you want to search for in JSON is not necessarily the best design of the schema. — Rick James
– Rick James, Commented Jul 17, 2017 at 21:50

wchiquito · Accepted Answer · 2017-07-18 03:35:45Z

I'm not sure if in a single query you can achieve what you need.

The following stored procedure, perhaps, can give you some ideas (stored procedure works for MariaDB and for 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)

Modify the code as you need it.

See db<>fiddle for MariaDB (10.2.6) and db-fiddle for MySQL (5.7.17).

Collectives™ on Stack Overflow

MariaDB/MySQL - How to query JSON based on key of nested object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related