arrays - $lookup 两层

Question

我是 MongoDB 和 NoSQL 的新手，我正在尝试(在相当有限的时间内)了解它是否可以完成我们已经将数据作为 json 文件的快速演示项目的工作。我正在尝试使用 Mongo 测试不同的查询场景，但有一种场景让我卡住了。想象一下，我有 3 个 mongodb 集合，代表具有类别和子类别的产品。每个项目可能有很多类别，每个类别可能有很多子类别。这是一个简单的数据示例:

产品

{ "id" : "1", "name" : "product1", "categories": ["cat1_id", "cat2_id"] }
{ "id" : "2", "name" : "product2", "categories": ["cat1_id"]}
{ "id" : "3", "name" : "product3", "categories": ["cat3_id"}

类别

{ "id" : "cat1_id", "name" : "cat1", "sub_categories": ["subcat1_id", "scat2_id"]}
{ "id" : "cat2_id", "name" : "cat2", "sub_categories": ["subcat3_id"]}
{ "id" : "cat3_id", "name" : "cat3", "sub_categories": ["subcat1_id"]}

子类别

{ "id" : "subcat1_id", "name" : "sub cat1"}
{ "id" : "subcat2_id", "name" : "sub cat2"}
{ "id" : "subcat3_id", "name" : "sub cat3"}

我正在尝试查找 3 个集合，以便将类别和子类别的数据获取到产品文档中:

{ 
  "id" : "1", 
  "name" : "product1", 
  "categories": [{
    "id": "cat1_id", 
    "name": "cat1", 
    "sub_categories": [
      { "id" : "subcat1_id", "name" : "sub cat1"}
      { "id" : "subcat2_id", "name" : "sub cat2"}
    ]
  }, {
    "id": "cat2_id", 
    "name": "cat2", 
    "sub_categories": [
      { "id" : "subcat1_id", "name" : "sub cat1"}
    ]
  }]
}

我正在执行 2 次查找并尝试使用 unwind 的多个选项，但如果产品有 2 个类别，我总是只获取第一个类别的子类别详细信息 - 第二个类别的子类别的详细信息已经消失并且即使对于类别 2，我也会得到类别 1 的子类别。

这是查询和我得到的结果:

查询:

db.product.aggregate([
    $lookup: {
        from: "category",
        localField: "categories",
        foreignField: "id",
        as: "categories_obj"
    },
    $unwind: "categories",
    $unwind: "categories_obj.sub_categories",
    $lookup: {
        from: "sub_category",
        localField: "categories_obj.sub_categories",
        foreignField: "id",
        as: "sub_category_obj"
    }
])

结果:

{
  "id" : "1", 
  "name" : "product1",
  "categories": "cat1_id"
  "categories_obj" : [
     { "id" : "cat1_id", "name" : "cat1", "sub_categories": ["subcat1_id", 
     "scat2_id"]}
     { "id" : "cat2_id", "name" : "cat2", "sub_categories": ["subcat3_id"]}
  ],
  "sub_category_obj": [
     { "id" : "subcat1_id", "name" : "sub cat1"}
     { "id" : "subcat2_id", "name" : "sub cat2"}
  ]
}

{
  "id" : "1", 
  "name" : "product1",
  "categories": "cat2_id"
  "categories_obj" : [
     { "id" : "cat1_id", "name" : "cat1", "sub_categories": ["subcat1_id", 
     "scat2_id"]}
     { "id" : "cat2_id", "name" : "cat2", "sub_categories": ["subcat3_id"]}
  ],
  "sub_category_obj": [
     { "id" : "subcat1_id", "name" : "sub cat1"}
     { "id" : "subcat2_id", "name" : "sub cat2"}
  ]
}

从结果可以看出，cat1和cat2都显示了子类1和2(属于cat1)的详细信息，但是子类3(属于cat2)缺失了。一旦我能够获取所有子类别的详细信息，我将尝试 $group、$project 等来获取上述所需的数据格式，但我不知道如何获取所有子类别的详细信息.

请在这件事上给予我帮助。我希望 MongoDB 可以实现。
能够以所述格式获取数据的查询也将受到欢迎!
谢谢!

Answer 1

您可以在 mongodb 3.6 及更高版本中使用以下聚合

Project.aggregate([
  { "$lookup": {
    "from": Category.collection.name,
    "let": { "categories": "$categories" },
    "pipeline": [
      { "$match": { "$expr": { "$in": [ "$_id", "$$categories" ] } } },
      { "$lookup": {
        "from": SubCategory.collection.name,
        "let": { "sub_categories": "$sub_categories" },
        "pipeline": [
          { "$match": { "$expr": { "$in": [ "$_id", "$$sub_categories" ] } } }
        ],
        "as": "sub_categories"
      }}
    ],
    "as": "categories"
  }}
])