如何将关联表B的匹配值以JSON数组更新至表A的json_field？

要实现将表B中与A.id匹配的value值以JSON数组形式更新到A表的json_field，需根据你使用的数据库类型选择对应语句，以下是主流数据库的实现方式：

MySQL/MariaDB #

使用JSON_ARRAYAGG函数直接聚合生成JSON数组，推荐两种写法：

子查询方式 #

UPDATE A
SET json_field = (
    SELECT JSON_ARRAYAGG(value)
    FROM B
    WHERE B.a_id = A.id
);

JOIN分组方式 #

UPDATE A
JOIN (
    SELECT a_id, JSON_ARRAYAGG(value) AS values_json
    FROM B
    GROUP BY a_id
) AS B_agg ON A.id = B_agg.a_id
SET A.json_field = B_agg.values_json;

PostgreSQL #

使用json_agg函数聚合生成JSON数组：

子查询方式 #

UPDATE A
SET json_field = (
    SELECT json_agg(value)
    FROM B
    WHERE B.a_id = A.id
);

JOIN分组方式 #

UPDATE A
SET json_field = B_agg.values_json
FROM (
    SELECT a_id, json_agg(value) AS values_json
    FROM B
    GROUP BY a_id
) AS B_agg
WHERE A.id = B_agg.a_id;

SQL Server #

• 若使用SQL Server 2022及以上版本，可直接用JSON_ARRAYAGG：

UPDATE A
SET json_field = (
    SELECT JSON_ARRAYAGG(value)
    FROM B
    WHERE B.a_id = A.id
);

• 低版本可通过STRING_AGG拼接字符串后转JSON：

UPDATE A
SET json_field = JSON_QUERY('[' + STRING_AGG(CAST(value AS VARCHAR(MAX)), ',') + ']')
FROM A
JOIN B ON A.id = B.a_id
GROUP BY A.id;

原语句问题说明 #

你之前尝试的语句没有为每条A记录单独关联对应的B数据，而是查询了所有匹配的value，没有按a_id分组对应到具体的A记录，因此无法正确赋值。

内容的提问来源于stack exchange，提问作者tweekz