MYSQL提取json包含的json元素 [英] MYSQL extract json elements where the json contains
本文介绍了MYSQL提取json包含的json元素的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我得到了orders表，其中orders.items为JSON
I'v got table orders with orders.items as 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"
}
}
我只想提取特定类别的项目
i want to extract only items that in specific category
仅提取类别为"9"的项目的预期结果:
expected result of extracting only items with category "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"的项
so far i manged to get all orders.items cell where there item with category = "9"
SELECT
`id`,
JSON_EXTRACT(`orders`.`items`,
'$')
FROM
`orders`
WHERE
JSON_CONTAINS(
JSON_EXTRACT(`orders`.`items`,
'$.*.category'),
'"9"'
)
推荐答案
lib_mysqludf_preg 用户定义的函数(UDF):
lib_mysqludf_preg user-defined function (UDF) was used:
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 .
In MariaDB you can use the built-in function REGEXP_REPLACE, see dbfiddle.
这篇关于MYSQL提取json包含的json元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
