MySQL在GROUP BY中合并JSON数组

huangapple go评论87阅读模式
英文:

MySQL merging json arrays in group by

问题

我正在尝试在按组合并标量数组 JSON 字段时,将所有不同的值放入一个列表中。

考虑以下表格:

CREATE TABLE transaction
(
    id                 INT UNSIGNED AUTO_INCREMENT,
    source_account_id  VARCHAR(32)                                          NOT NULL,
    target_account_ids JSON                                                 NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB CHARSET utf8mb4;

source_account_ids 是一个简单的字符串数组,例如 '["account1", "account2"]'
我想要收集单个源的所有目标帐户 ID,以获得统一的结果。
例如:

id source_account_id target_account_ids
1. account1 '["account1", "account2"]'
2. account1 '["account1", "account3"]'

所需的结果集应为:

source_account_id target_account_ids
account1 '["account1", "account2", "account3"]'

我尝试使用 JSON_ARRAYAGG 进行尝试,但它只是将数组添加到另一个数组中,基本上导致了一个"无限"数组。

英文:

I'm trying to merge a scalar array json field within a group by to have all the distinct values in one list.

Consider the following table:

CREATE TABLE transaction
(
    id                 INT UNSIGNED AUTO_INCREMENT,
    source_account_id  VARCHAR(32)                                          NOT NULL,
    target_account_ids JSON                                                 NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB CHARSET utf8mb4;

source_account_ids is a simple array of strings for example '["account1", "account2"]'.
I'd like to gather all the target_account_ids of a single source to have a unified result.
For example:

id source_account_id target_account_ids
1. account1 '["account1", "account2"]'
2. account1 '["account1", "account3"]'

And the desired result set would be:

source_account_id target_account_ids
account1 '["account1", "account2", "account3"]'

I tried to play around with JSON_ARRAYAGG but it just adds the arrays within another array and basically results in an "endless" array.

答案1

得分: 1

你需要使用JSON_TABLE()函数来拆分数组,然后使用DISTINCT来减少值,最后可以使用JSON_ARRAYAGG()函数重新组合它们。

select source_account_id, json_arrayagg(target_account_id) as target_account_ids
from ( 
  select distinct source_account_id, j.account_id as target_account_id
  from transaction
  cross join json_table(target_account_ids, '$[*]' columns (account_id varchar(32) path '$')) as j
) as t
group by source_account_id;

GROUP_CONCAT()函数支持在其参数中使用DISTINCT关键字,但JSON_ARRAYAGG()函数不支持(这个功能已经被请求:https://bugs.mysql.com/bug.php?id=91993)。

如果这看起来像是很多不必要的工作,或者如果你不能使用JSON_TABLE()因为你仍然在使用MySQL 5.7,那么你应该将多值属性存储在普通的行和列中,而不是使用JSON。

英文:

You have to explode the array with JSON_TABLE(), then reduce the values with DISTINCT, then you can recombine them with JSON_ARRAYAGG().

select source_account_id, json_arrayagg(target_account_id) as target_account_ids
from ( 
  select distinct source_account_id, j.account_id as target_account_id
  from transaction
  cross join json_table(target_account_ids, '$[*]' columns (account_id varchar(32) path '$')) as j
) as t
group by source_account_id;

GROUP_CONCAT() supports a DISTINCT keyword in its argument, but JSON_ARRAYAGG() doesn't (this feature has been requested: https://bugs.mysql.com/bug.php?id=91993).

If this seems like a lot of needless work, or if you can't use JSON_TABLE() because you're still using MySQL 5.7, then you should store multi-valued attributes in normal rows and columns, instead of using JSON.

huangapple
  • 本文由 发表于 2023年2月7日 03:47:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/75365900.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定