PostgreSQL中合并不同列多表数据，按组生成时间有序JSON数组

核心思路 #

先将两张表的数据统一时间排序字段，合并后按groupId和时间排序，最后将同组的所有数据聚合为有序的JSON数组。

MySQL 实现（5.7及以上版本，支持JSON函数） #

SELECT 
    groupId,
    JSON_ARRAYAGG(item ORDER BY sort_time) AS allitems
FROM (
    -- 处理title表，统一时间字段为sort_time，转成JSON对象
    SELECT 
        groupId,
        createdDate AS sort_time,
        JSON_OBJECT(
            'titleId', titleId,
            'createdDate', createdDate,
            'groupId', groupId,
            'text', text
            -- 如需保留其他字段，继续添加键值对即可
        ) AS item
    FROM title
    
    UNION ALL
    
    -- 处理photo表，统一时间字段为sort_time，转成JSON对象
    SELECT 
        groupId,
        timestamp AS sort_time,
        JSON_OBJECT(
            'photoId', photoId,
            'timestamp', timestamp,
            'groupId', groupId,
            'url', url
            -- 如需保留其他字段，继续添加键值对即可
        ) AS item
    FROM photo
) AS combined_data
GROUP BY groupId;

说明 #

1. 子查询通过UNION ALL合并两张表的数据，同时将各自的时间字段统一命名为sort_time，方便后续排序。
2. 用JSON_OBJECT将每行数据转换为JSON格式，保留原表需要的所有字段。
3. 外层查询通过JSON_ARRAYAGG(item ORDER BY sort_time)将同groupId的所有JSON对象按时间顺序聚合为一个JSON数组。

PostgreSQL 实现 #

SELECT 
    groupId,
    json_agg(item ORDER BY sort_time) AS allitems
FROM (
    -- 处理title表，转成JSON对象
    SELECT 
        groupId,
        createdDate AS sort_time,
        row_to_json(title) AS item
    FROM title
    
    UNION ALL
    
    -- 处理photo表，转成JSON对象
    SELECT 
        groupId,
        timestamp AS sort_time,
        row_to_json(photo) AS item
    FROM photo
) AS combined_data
GROUP BY groupId;

说明 #

1. 使用row_to_json可以直接将整行数据转换为JSON对象，无需手动指定每个字段，更简洁。
2. 同样通过UNION ALL合并数据，统一时间字段后排序，最后用json_agg聚合为有序JSON数组。

结果验证 #

执行上述SQL后，返回的结果将与预期结构一致：每个groupId对应一行，allitems列是按时间先后排序的title和photo数据组成的JSON数组。

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