如何将PostgreSQL左连接结果分组为关联数组（Drizzle ORM）

问题场景 #

使用Drizzle ORM执行左连接查询：

const data = await db
  .select()
  .from(projects)
  .where(eq(projects.slug, slug))
  .leftJoin(projectPdfs, eq(projects.id, projectPdfs.projectId));

查询返回包含重复projects对象的数组，每个对象对应一条pdf数据：

[
  {
    projects: { id: 1, name: 'redacted', slug: 'redacted' },
    pdfs: {
      id: 1,
      assetUrl: 'redacted',
      projectId: 1,
      requestTimeMs: '4859.872999999672'
    }
  },
  {
    projects: { id: 1, name: 'redacted', slug: 'redacted' },
    pdfs: {
      id: 2,
      assetUrl: 'redacted',
      projectId: 1,
      requestTimeMs: '4859.872999999672'
    }
  }
]

期望得到单个project对象，关联的pdf数据合并为数组：

{
  project: { id: 1, name: 'redacted', slug: 'redacted' },
  pdfs: [
    {
      id: 1,
      assetUrl: 'redacted',
      projectId: 1,
      requestTimeMs: '4859.872999999672'
    },
    {
      id: 2,
      assetUrl: 'redacted',
      projectId: 1,
      requestTimeMs: '4859.872999999672'
    }
  ]
}

尝试groupBy未得到预期结果，需解决方法。

解决方法 #

方法1：数据库层面聚合（PostgreSQL） #

利用PostgreSQL的array_agg函数在查询中直接聚合pdf数据，同时指定返回字段并按project主键分组：

import { sql } from 'drizzle-orm';

const [result] = await db
  .select({
    project: projects,
    pdfs: sql`array_agg(${projectPdfs})`.as('pdfs'),
  })
  .from(projects)
  .where(eq(projects.slug, slug))
  .leftJoin(projectPdfs, eq(projects.id, projectPdfs.projectId))
  .groupBy(projects.id);

若需处理无pdf时的空值，可使用coalesce确保返回空数组而非null：

pdfs: sql`coalesce(array_agg(${projectPdfs}), '[]'::jsonb)`.as('pdfs'),

方法2：客户端层面处理结果 #

若无需修改查询逻辑，可在获取结果后手动合并数据：

const data = await db
  .select()
  .from(projects)
  .where(eq(projects.slug, slug))
  .leftJoin(projectPdfs, eq(projects.id, projectPdfs.projectId));

if (data.length === 0) {
  return null; // 处理未找到项目的情况
}

const project = data[0].projects;
const pdfs = data.map(item => item.pdfs).filter(Boolean);

const mergedResult = { project, pdfs };

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