如何在MySQL 8中更改JSON中数组随机元素的值？

dced5bon 于 2023-04-04 发布在 Mysql

关注(0)|答案(1)|浏览(102)

我有一个如下的JSON结构：

{

"details": {
  "product_name": "Example Product",
  "description": "This is an example product",
  "price": 10.99,
  "dimensions": {
    "width": 10,
    "height": 15,
    "depth": 5
  },
  "reviews": [
    {
      "author": "John Smith",
      "rating": 4,
      "comment": "Great product, would buy again"
    },
    {
      "author": "Jane Doe",
      "rating": 3,
      "comment": "Product was okay, not great"
    }
  ]
 }
}

我需要以某种方式更改数组“评论”中的“作者”字段的值，该字段的“评级”等于3。
我试过这样的方法：

UPDATE my_table SET json_data = JSON_REPLACE(json_data, 
    JSON_UNQUOTE(
        REPLACE(
            JSON_SEARCH(json_data, 'one', 3, NULL, '$.details.reviews[*].rating'), 
            '.rating', '.author'
        )
    ),
    'New Author'
)
WHERE JSON_SEARCH(json_data, 'one', 3, NULL, '$.details.reviews[*].rating') IS NOT NULL;

但没有成功。
请解释一下我做错了什么？

mysql

来源：https://stackoverflow.com/questions/75917675/how-to-change-value-in-random-element-of-array-in-json-in-mysql-8

1条答案

按热度按时间

vkc1a9a21#

JSON_SEARCH函数只处理documentation中提到的字符串标量，所以你的数字必须用引号括起来。
为了使其工作，我们首先需要使用REGEXP_REPLACE将引号添加到数字中，如下所示：

select json_data, REGEXP_REPLACE(json_data, '(:\\s*)(\\d+\\.?\\d*)', '$1"$2"')
from my_table;

将返回JSON_SEARCH的有效json：

{"details": {"price": "10.99", "reviews": [{"author": "John Smith", "rating": "4", "comment": "Great product, would buy again"}, {"author": "Jane Doe", "rating": "3", "comment": "Product was okay, not great"}], "dimensions": {"depth": "5", "width": "10", "height": "15"}, "description": "This is an example product", "product_name": "Example Product"}}

因此，您的查询可以是：

select json_data, REGEXP_REPLACE(json_data, '(:\\s*)(\\d+\\.?\\d*)', '$1"$2"')

从我的_table;

UPDATE my_table SET json_data = JSON_REPLACE(json_data, 
    JSON_UNQUOTE(
        REPLACE(
            JSON_SEARCH(REGEXP_REPLACE(json_data, '(:\\s*)(\\d+\\.?\\d*)', '$1"$2"'), 'one', 3, NULL, '$.details.reviews[*].rating'), 
            '.rating', '.author'
        )
    ),
    'New Author'
)
WHERE JSON_SEARCH(REGEXP_REPLACE(json_data, '(:\\s*)(\\d+\\.?\\d*)', '$1"$2"'), 'one', 3, NULL, '$.details.reviews[*].rating') IS NOT NULL;

结果：

{"details": {"price": 10.99, "reviews": [{"author": "John Smith", "rating": 4, "comment": "Great product, would buy again"}, {"author": "New Author", "rating": 3, "comment": "Product was okay, not great"}], "dimensions": {"depth": 5, "width": 10, "height": 15}, "description": "This is an example product", "product_name": "Example Product"}}

Demo here

赞(0）回复(0）举报 2023-04-04

我来回答

如何在MySQL 8中更改JSON中数组随机元素的值？

1条答案

相关问题

热门标签

最新问答