如何根据Mysql 8.0.26中的两个条件更新JSON数组中的元素
如何根据Mysql 8.0.26中的两个条件更新JSON数组中的元素
提问于 2022-06-15 22:14:36
mysql列中有以下json数据：
{
"components":[
{
"url":"www.google.com",
"order":3,
"accountId":"123",
"autoPilot":true,
},
{
"url":"www.youtube.com",
"order":7,
"accountId":"987",
"autoPilot":false,
},
"addTabs":true,
"activateHomeSection":true
}我需要基于和自动驾驶仪属性更新accountId url属性。
例如，我通过：
- accountId = 123，
- 自动驾驶仪=真，
- url = 'facebook.com‘
结果: AccountId和自动驾驶仪匹配-> www.google.com url更改为www.facebook.com
{
"components":[
{
"url":"www.google.com",
"order":3,
"accountId":"123",
"autoPilot":true
},
...直到现在，我才成功地编写了以下代码：
select j.url from users, json_table(custom_page, '$.components[*]' columns (
autoPilot boolean path '$.autoPilot',
accountId varchar(36) path '$.accountId',
url TEXT path '$.url')) as j
where username='mihai2nd' AND j.autoPilot = true and j.accountId='123'; 我计划首先检索url，然后使用regex函数更新它。但是这种方式在性能上并不好，因为我需要发送2个查询。我还用JSON_SEARCH / JSON_CONTAINS / JSON_EXTRACT尝试了其他方法，但都失败了。
你知道男人们如何在不影响表现的情况下实现这一点吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-06-15 22:53:56
最好的方法是以良好的性能将属性存储在带有行和列的普通表中，而不是将它们存储在JSON中。如果您将数据存储在JSON中，它将抵制使用索引进行优化。
MySQL 8.0.17及更高版本支持多值指数，它允许您在JSON数组上创建索引。不幸的是，您只能引用一列作为多值索引，因此您需要选择哪些属性最有可能帮助提高性能，如果通过索引查找是可搜索的。假设是accountId。
mysql> create table user (custom_page json);
mysql> alter table user add index bk1 ((CAST(custom_page->'$.components[*].accountId' AS UNSIGNED ARRAY)));
mysql> explain select * from user where 12 member of (custom_page->'$.components[*].accountId');
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ref | bk1 | bk1 | 9 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+在上面的示例中可以看到，优化器将使用我在使用MEMBER OF()谓词进行搜索时创建的索引。
这至少会将搜索范围缩小到找到值的行。然后，您可以使用JSON_TABLE()方法来准确提取哪些条目具有所需的属性，同时知道搜索将仅限于与MEMBER OF()谓词匹配的行。
select user.id, j.* from user
cross join json_table(custom_page, '$.components[*]' columns(
rownum for ordinality,
autoPilot boolean path '$.autoPilot',
accountId varchar(36) path '$.accountId',
url text path '$.url')
) as j
where 123 member of (custom_page->'$.components[*].accountId');
+----+--------+-----------+-----------+-----------------+
| id | rownum | autoPilot | accountId | url |
+----+--------+-----------+-----------+-----------------+
| 1 | 1 | 1 | 123 | www.google.com |
| 1 | 2 | 0 | 987 | www.youtube.com |
+----+--------+-----------+-----------+-----------------+现在您可以过滤这些生成的行：
select user.id, j.* from user
cross join json_table(custom_page, '$.components[*]' columns(
rownum for ordinality,
autoPilot boolean path '$.autoPilot',
accountId varchar(36) path '$.accountId',
url text path '$.url')
) as j
where 123 member of (custom_page->'$.components[*].accountId')
and j.autoPilot = true
and j.accountId = 123;
+----+--------+-----------+-----------+----------------+
| id | rownum | autoPilot | accountId | url |
+----+--------+-----------+-----------+----------------+
| 1 | 1 | 1 | 123 | www.google.com |
+----+--------+-----------+-----------+----------------+这是SELECT，但您需要更新。
with cte as (
select user.id, j.* from user
cross join json_table(custom_page, '$.components[*]' columns(
rownum for ordinality,
autoPilot boolean path '$.autoPilot',
accountId varchar(36) path '$.accountId',
url text path '$.url')
) as j
where 123 member of (custom_page->'$.components[*].accountId')
and j.autoPilot = true
and j.accountId = 123
)
update user cross join cte
set custom_page = json_set(custom_page, concat('$.components[', cte.rownum-1, '].url'), 'facebook.com')
where cte.id = user.id;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0然后检查它是否更新了您想要的方式：
mysql> select id, json_pretty(custom_page) from user;
+----+--------------------------+
| id | json_pretty(custom_page) |
+----+--------------------------+
| 1 | {
"addTabs": true,
"components": [
{
"url": "facebook.com",
"order": 3,
"accountId": "123",
"autoPilot": true
},
{
"url": "www.youtube.com",
"order": 7,
"accountId": "987",
"autoPilot": false
}
],
"activateHomeSection": true
} |
+----+--------------------------+坦率地说，这是不必要的困难和复杂。如果以这种方式存储JSON数据，开发任何更改JSON数据的代码都是非常耗时的。如果您需要像对待一组离散的行和列一样使用JSON，那么您应该创建一个具有普通行和列的表。然后，几乎所有与JSON函数有关的操作都变得简单明了：
CREATE TABLE page_components (
id INT PRIMARY KEY,
accountId VARCHAR(36) NOT NULL,
autoPilot BOOLEAN NOT NULL DEFAULT FALSE,
`order` INT NOT NULL DEFAULT 1,
url TEXT NOT NULL,
KEY (accountId, autoPilot)
);...populate它与您将存储在JSON中的数据..。
UPDATE page_components
SET url = 'facebook.com'
WHERE accountId = 123 AND autoPilot = true;我越多地看到关于MySQL中JSON的堆栈溢出问题，我就越认为这是添加到关系数据库中的最糟糕的特性。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72638409
复制相关文章
相似问题
腾讯云开发者
