如何在Supabase中通过数组聚合实现按parentid分组查询？

你可以利用PostgreSQL的json_agg函数在数据库端完成聚合，以下是两种实现方式：

1. 创建视图 #

假设你的源表名为parent_child（请替换为实际表名），执行以下SQL创建视图：

CREATE VIEW aggregated_parent_children AS
SELECT
  parentid,
  json_agg(json_build_object('childname', childname)) AS children
FROM parent_child
GROUP BY parentid;

创建完成后，直接查询该视图（或通过Supabase API调用这个视图），就能得到目标聚合结果：

SELECT * FROM aggregated_parent_children;

2. 创建SQL函数 #

如果需要更灵活的调用方式，可以创建返回表类型的函数：

CREATE OR REPLACE FUNCTION get_aggregated_children()
RETURNS TABLE(parentid INT, children JSON) AS $$
BEGIN
  RETURN QUERY
  SELECT
    parentid,
    json_agg(json_build_object('childname', childname)) AS children
  FROM parent_child
  GROUP BY parentid;
END;
$$ LANGUAGE plpgsql;

调用函数获取结果：

SELECT * FROM get_aggregated_children();

核心逻辑说明 #

• json_build_object将childname字段包装为{"childname": "xxx"}的对象格式
• json_agg把同一parentid下的所有子对象聚合为数组
• 数据库端聚合能减少客户端传输与处理的数据量，比客户端聚合更高效

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