如何按物种分组聚合动物数据？SQL分组查询求助

问题背景 #

现有animal表，数据插入语句如下：

INSERT INTO animal (id, species, age)
  VALUES ("titi", "cat", 3), ("mimi", "cat", 4), ("kiki", "dog", 5);

期望得到按物种分组，每个组包含对应成员列表的结果，格式如下：

[
    {
        "species": "cat",
        "members": [
          { "id": "animal:mimi", "age": 4 },
          { "id": "animal:titi", "age": 3 }
        ]
    },
    {
        "species": "dog",
        "members": [
          { "id": "animal:kiki", "age": 5 }
        ]
    }
]

尝试的SQL语句出现语法错误：

SELECT species, array::group({id, age}) AS members FROM animal GROUP BY species;

报错信息：

1 | SELECT species, array::group({id, age}) AS members FROM animal GROUP BY species;
  |                              ^ expected this delimiter to close

正确实现方法 #

不同SQL数据库的JSON/数组聚合语法略有不同，以下是主流数据库的实现方式：

PostgreSQL #

使用json_build_object构造成员对象，json_agg聚合为数组：

SELECT 
  species,
  json_agg(json_build_object('id', 'animal:' || id, 'age', age)) AS members
FROM animal
GROUP BY species
ORDER BY species;

MySQL 8.0+ #

使用JSON_OBJECT构造对象，JSON_ARRAYAGG聚合为数组：

SELECT 
  species,
  JSON_ARRAYAGG(
    JSON_OBJECT('id', CONCAT('animal:', id), 'age', age)
  ) AS members
FROM animal
GROUP BY species
ORDER BY species;

SQLite 3.33.0+ #

使用json_object构造对象，json_group_array聚合为数组：

SELECT 
  species,
  json_group_array(
    json_object('id', 'animal:' || id, 'age', age)
  ) AS members
FROM animal
GROUP BY species
ORDER BY species;

错误原因说明 #

你使用的array::group({id, age})并非标准SQL语法，也不是主流数据库支持的函数。要实现嵌套聚合，需要依赖对应数据库提供的JSON或数组聚合函数，同时正确构造包含animal:前缀的id字段。

内容的提问来源于stack exchange，提问作者Thomas Hügel