PostgreSQL如何将结果集每行转为JSON并合并为单个数组

问题背景 #

现有三张数据库表结构如下：

• Owner表：

Owner
-----------------------
id         | long
name       | string

• Animal表：

Animal
-----------------------
id         | long
status     | string
external_d | string
region     | long (外键关联Region表id)
owner_id    | long (外键关联Owner表id)

• Region表：

Region
-----------------------
id         | long
name       | string

需求是从Animal表中筛选owner_id=12的记录，将每条记录转为包含id、status、externalId、regionId的JSON对象，最终合并为单个JSON数组，期望输出示例：

[
   {id: 3, status: 'alive', externalId: 'abc90', regionId: 2},
   {id: 9, status: 'dead', externalId: 'xuy12', regionId: 2},
   {id: 13, status: 'alive', externalId: 'ter34', regionId: 2}
]

当前使用的查询语句执行后，每条记录会单独生成一个数组，无法合并为目标格式：

SELECT JSON_ARRAY(
    JSON_OBJECT(
        'id', id, 
        'externalId', externalId, 
        'status', status, 
        'regionId', regionId
        )
    ) as final_data
    FROM 
    (SELECT 
        a.id as id, 
        a.external_id as externalId, 
        a.status as status, 
        a.region_id as regionId 
    FROM 
        myDB.animal a 
    WHERE a.owner_id=12) as data;

解决方案 #

问题根源是JSON_ARRAY()会为每一行单独生成数组，要实现多行聚合为单个数组，需使用JSON_ARRAYAGG()函数（MySQL 5.7.22及以上版本支持），该函数可将多行的JSON对象聚合为一个统一的JSON数组。

修正后的查询语句：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id', id, 
        'externalId', externalId, 
        'status', status, 
        'regionId', regionId
    )
) as final_data
FROM (
    SELECT 
        a.id as id, 
        a.external_d as externalId,  -- 原表字段为external_d，注意对应
        a.status as status, 
        a.region as regionId         -- 原表关联Region的字段是region，不是region_id
    FROM 
        myDB.animal a 
    WHERE a.owner_id=12
) as data;

关键说明 #

1. JSON_ARRAYAGG()会遍历子查询返回的所有结果行，将每行生成的JSON_OBJECT()实例收集到同一个数组中，最终输出单个包含所有目标对象的JSON数组。
2. 注意修正子查询中的字段映射：原Animal表中存储外部ID的字段是external_d，关联Region的字段是region，需与表结构对应，避免字段不存在的错误。

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