从MongoDB迁移至PostgreSQL：编写PostgreSQL aggregation管道查询

我们的Node.js Express应用需要从MongoDB迁移到PostgreSQL，现有API大量使用MongoDB的聚合框架，需要把这些聚合查询改写为PostgreSQL查询。已知规则：一个应用仅属于一个产品，一个项目仅属于一个应用，必须严格输出指定的嵌套JSON结构。

示例数据 #

[
  {
    "product": "PD1",
    "application": "A",
    "project": "PR1",
    "version": "1"
  },
  {
    "product": "PD1",
    "application": "A",
    "project": "PR2",
    "version": "3"
  },
  {
    "product": "PD1",
    "application": "B",
    "project": "PR3",
    "version": "2"
  },
  {
    "product": "PD2",
    "application": "C",
    "project": "PR4",
    "version": "5"
  },
  {
    "product": "PD2",
    "application": "C",
    "project": "PR5",
    "version": "2"
  }
]

原MongoDB聚合查询 #

let data = await CompanyModel.aggregate([
  {
    $group: {
      "_id": "$application",
      "projects": {
        $push: {
          "project": "$project",
          "version": "$version"
        }
      },
      "product": {$first: "$product"}
    }
  },
  {
    $group: {
      "_id": "$product",
      "applications": {
        $push: {
          "application": "$_id",
          "projects": "$projects"
        }
      },
    }
}])

目标输出结构 #

[
  {
    "_id": "PD2",
    "applications": [
      {
        "application": "C",
        "projects": [
          {"project": "PR4", "version": "5"},
          {"project": "PR5", "version": "2"}
        ]
      }
    ]
  },
  {
    "_id": "PD1",
    "applications": [
      {
        "application": "B",
        "projects": [{"project": "PR3", "version": "2"}]
      },
      {
        "application": "A",
        "projects": [
          {"project": "PR1", "version": "1"},
          {"project": "PR2", "version": "3"}
        ]
      }
    ]
  }
]

核心问题与解决方案 #

PostgreSQL完全支持类似MongoDB的管道式分组逻辑，你可以用嵌套子查询或者**CTE（公共表表达式）**来实现多阶段聚合，两者本质都是将前一阶段的输出作为后一阶段的输入。以下是两种实现方式：

方式1：嵌套子查询 #

直接用嵌套结构，结合PostgreSQL的jsonb_build_object和jsonb_agg函数构造嵌套JSON：

SELECT
  product AS "_id",
  jsonb_agg(
    jsonb_build_object(
      'application', application,
      'projects', projects
    )
  ) AS "applications"
FROM (
  SELECT
    application,
    product,
    jsonb_agg(
      jsonb_build_object(
        'project', project,
        'version', version
      )
    ) AS projects
  FROM company_table -- 替换为你的实际表名
  GROUP BY application, product
) AS app_group
GROUP BY product;

方式2：CTE（管道式写法） #

CTE把每个聚合阶段拆分成独立的命名步骤，和MongoDB的聚合管道逻辑更对应，可读性更强：

WITH app_group AS (
  SELECT
    application,
    product,
    jsonb_agg(
      jsonb_build_object(
        'project', project,
        'version', version
      )
    ) AS projects
  FROM company_table -- 替换为你的实际表名
  GROUP BY application, product
)
SELECT
  product AS "_id",
  jsonb_agg(
    jsonb_build_object(
      'application', application,
      'projects', projects
    )
  ) AS "applications"
FROM app_group
GROUP BY product;

关键函数说明 #

• jsonb_build_object(key1, val1, key2, val2)：构造单个JSON对象，对应MongoDB里的字段映射。
• jsonb_agg(json_obj)：将多个JSON对象聚合为数组，对应MongoDB的$push操作。
• 由于已知一个应用仅属于一个产品，第一阶段按application和product分组是安全的，也可以用MAX(product)或MIN(product)替代直接分组，结果一致。

输出纯JSON数组 #

如果需要输出无列名的纯JSON数组，可再用json_agg聚合最终结果：

SELECT json_agg(result)
FROM (
  SELECT
    product AS "_id",
    jsonb_agg(
      jsonb_build_object(
        'application', application,
        'projects', projects
      )
    ) AS "applications"
  FROM (
    SELECT
      application,
      product,
      jsonb_agg(
        jsonb_build_object(
          'project', project,
          'version', version
        )
      ) AS projects
    FROM company_table
    GROUP BY application, product
  ) AS app_group
  GROUP BY product
) AS result;

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