MYSQL извлекает элементы json, в которых содержится json

У меня есть стол orders с orders,items как JSON

пример orders,items клетка:



{
"10": {
"name": "item 1",
"step": "1",
"price": "140",
"amount": "4",
"category": "9"},
"24": {
"name": "item 2",
"step": "1",
"price": "6.2",
"amount": "1",
"category": "5"},
"35": {
"name": "item 3",
"step": "1",
"price": "2.9",
"amount": "3",
"category": "1"},
"37": {
"name": "item 4",
"step": "1",
"price": "3.9",
"amount": "2",
"category": "9"}
}


я хочу извлечь только элементы, которые в определенной категории

Ожидаемый результат извлечения только предметов с категорией «9»:



{
"10": {
"name": "item 1",
"step": "1",
"price": "140",
"amount": "4",
"category": "9"},
"37": {
"name": "item 4",
"step": "1",
"price": "3.9",
"amount": "2",
"category": "9"}
}


до сих пор мне удалось получить все orders,items ячейка, где есть пункт с категорией = «9»


SELECT
`id`,
JSON_EXTRACT(`orders`.`items`,
'$')
FROM
`orders`
WHERE
JSON_CONTAINS(
JSON_EXTRACT(`orders`.`items`,
'$.*.category'),
'"9"'
)

0

Решение

lib_mysqludf_preg пользовательская функция (UDF) была использована:

mysql> DROP TABLE IF EXISTS `orders`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `orders` (
->   `id` SERIAL,
->   `items` JSON
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `orders`
->   (`items`)
-> VALUES ('
'>   {
'>     "10": {
'>       "name": "item 1",
'>       "step": "1",
'>       "price": "140",
'>       "amount": "4",
'>       "category": "9"'>     },
'>     "24": {
'>       "name": "item 2",
'>       "step": "1",
'>       "price": "6.2",
'>       "amount": "1",
'>       "category": "5"'>     },
'>     "35": {
'>       "name": "item 3",
'>       "step": "1",
'>       "price": "2.9",
'>       "amount": "3",
'>       "category": "1"'>     },
'>     "37": {
'>       "name": "item 4",
'>       "step": "1",
'>       "price": "3.9",
'>       "amount": "2",
'>       "category": "9"'>     }
'>   }'),
->   ('{
'>     "10": {
'>       "name": "item 1",
'>       "step": "1",
'>       "price": "141",
'>       "amount": "4",
'>       "category": "9"'>     }
'>   }'),
->   ('{
'>     "10": {
'>       "name": "item 1",
'>       "step": "1",
'>       "price": "141",
'>       "amount": "4",
'>       "category": "8"'>     }
'>   }');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT `id`, `items`
-> FROM `orders`;
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | items                                                                                                                                                                                                                                                                                                                                                        |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"10": {"name": "item 1", "step": "1", "price": "140", "amount": "4", "category": "9"}, "24": {"name": "item 2", "step": "1", "price": "6.2", "amount": "1", "category": "5"}, "35": {"name": "item 3", "step": "1", "price": "2.9", "amount": "3", "category": "1"}, "37": {"name": "item 4", "step": "1", "price": "3.9", "amount": "2", "category": "9"}} |
|  2 | {"10": {"name": "item 1", "step": "1", "price": "141", "amount": "4", "category": "9"}}                                                                                                                                                                                                                                                                      |
|  3 | {"10": {"name": "item 1", "step": "1", "price": "141", "amount": "4", "category": "8"}}                                                                                                                                                                                                                                                                      |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> SET @`category` := '9';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
-> GROUP_CONCAT('
'>   SELECT
'>     `id`,
'>     JSON_EXTRACT(CONCAT(\'[\', REPLACE(`items`, \'"}, "\', \'"}},{"\'), \']\'), ',
->       PREG_REPLACE('/^\\[|\\.\\\\\"\\d+\\\\\"\\.category|\\]$/i', '',
->         JSON_SEARCH(
->           CONCAT('[', REPLACE(`items`, '"}, "', '"}},{"'), ']'),
->           'all',
->           @`category`,
->           NULL,
->           '$**.category'
->         )
->       ), '
'>     ) `items`
'>   FROM `orders`
'>   WHERE `id` = ', `id`
->   SEPARATOR ' UNION ALL ') INTO @`sql`
-> FROM
->   `orders`
-> WHERE
->   JSON_CONTAINS(
->     JSON_EXTRACT(`items`,
->     '$**.category'),
->     CONCAT('"', @`category`, '"')
->   );
Query OK, 1 row affected (0.00 sec)

mysql> PREPARE `stmt` FROM @`sql`;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE `stmt`;
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | items                                                                                                                                                                              |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | [{"10": {"name": "item 1", "step": "1", "price": "140", "amount": "4", "category": "9"}}, {"37": {"name": "item 4", "step": "1", "price": "3.9", "amount": "2", "category": "9"}}] |
|  2 | {"10": {"name": "item 1", "step": "1", "price": "141", "amount": "4", "category": "9"}}                                                                                            |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE `stmt`;
Query OK, 0 rows affected (0.00 sec)

В MariaDB вы можете использовать встроенную функцию REGEXP_REPLACE, увидеть dbfiddle.

-1

Другие решения

Других решений пока нет …

По вопросам рекламы [email protected]