能否通过SQL查询获取含嵌套模型列表的品牌数据结果？

你想要的结果格式如下：

[{
  id: 1,
  brandName: "x",
  brandModels: [
    {id: 1, modelName: "abc", createdAt: "yyyy-mm-dd"},
    {id: 2, modelName: "def", createdAt: "yyyy-mm-dd"},
  ]
},
{
  id: 2,
  brandName: "y",
  brandModels: [
    {id: 4, modelName: "ghi", createdAt: "yyyy-mm-dd"},
    {id: 5, modelName: "jkl", createdAt: "yyyy-mm-dd"},
  ]
}]

现有数据库表结构：

BrandsTable
{id, brandName, brand_id}

ModelsTable
{id, modelName, createdAt}

前提说明 #

首先需要明确：ModelsTable应该存在brand_id字段（用于关联BrandsTable的id），否则两张表无法建立关联，无法获取品牌对应的车型数据。

SQL可以实现该需求 #

现代关系型数据库（如PostgreSQL、MySQL 8.0+）都支持JSON聚合函数，能直接生成嵌套格式的结果：

PostgreSQL实现

SELECT
  b.id,
  b.brandName,
  json_agg(
    json_build_object(
      'id', m.id,
      'modelName', m.modelName,
      'createdAt', m.createdAt
    )
  ) AS brandModels
FROM BrandsTable b
LEFT JOIN ModelsTable m ON b.id = m.brand_id
GROUP BY b.id, b.brandName;

MySQL 8.0+实现

SELECT
  b.id,
  b.brandName,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'id', m.id,
      'modelName', m.modelName,
      'createdAt', m.createdAt
    )
  ) AS brandModels
FROM BrandsTable b
LEFT JOIN ModelsTable m ON b.id = m.brand_id
GROUP BY b.id, b.brandName;

MongoDB实现 #

MongoDB作为文档型数据库，天然适配嵌套结构，实现起来更直观：

db.brands.aggregate([
  {
    $lookup: {
      from: "models",
      localField: "id",
      foreignField: "brand_id",
      as: "brandModels"
    }
  },
  {
    $project: {
      _id: 0,
      id: "$id",
      brandName: "$brandName",
      brandModels: {
        $map: {
          input: "$brandModels",
          as: "model",
          in: {
            id: "$$model.id",
            modelName: "$$model.modelName",
            createdAt: "$$model.createdAt"
          }
        }
      }
    }
  }
])

总结 #

你的猜测有误，SQL完全可以实现这种嵌套格式的查询结果，最终返回的JSON数据可以直接转换为JS对象；MongoDB确实也能轻松实现，且语法更贴合文档结构。

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