如何查询资产关联的所有交易并以JSON数组形式返回？

核心思路 #

先通过LEFT JOIN将资产表与交易表关联（匹配from_id或to_id），再用数据库的JSON聚合函数，将每个资产对应的所有交易合并成JSON数组。

PostgreSQL 实现 #

SELECT
    a.id,
    a.title,
    json_agg(DISTINCT json_build_object('id', t.id, 'title', t.title)) AS transactions
FROM assets a
LEFT JOIN transactions t ON a.id = t.from_id OR a.id = t.to_id
GROUP BY a.id, a.title;

• json_build_object：将交易的id和title组装成JSON对象
• json_agg：将同一资产的所有交易对象聚合为JSON数组
• DISTINCT：避免同一交易因from_id和to_id指向同一资产时重复出现

MySQL 实现 #

SELECT
    a.id,
    a.title,
    IFNULL(
        JSON_ARRAYAGG(DISTINCT JSON_OBJECT('id', t.id, 'title', t.title)),
        JSON_ARRAY()
    ) AS transactions
FROM assets a
LEFT JOIN transactions t ON a.id = t.from_id OR a.id = t.to_id
GROUP BY a.id, a.title;

• JSON_OBJECT：构造交易的JSON对象
• JSON_ARRAYAGG：聚合为JSON数组
• IFNULL(..., JSON_ARRAY())：处理无关联交易的情况，返回空数组而非NULL

为什么之前的方法不生效？ #

• 拆分expenses和incomes的方式是将交易按方向分开，无法合并成一个统一的数组；
• 直接GROUP BY未使用聚合函数，只会返回每组的第一条交易，无法收集所有关联记录。

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