时间戳

首页 > 解决方案 > 嵌套数组对象的 $lookup 聚合

mongodb - 嵌套数组对象的 $lookup 聚合

问题描述

我有category包含子类别嵌入对象的集合。我需要编写聚合脚本来首先从这个集合查找到storelisting集合。然后将商店列表字段添加到相应的子类别中。

类别

{
  "_id": "",
  "categoryName": "",
  "subCategories": [
    {
      "subCategoryId": "",
      "subCategoryName": "",
      "storeListingIds": [
        "1",
        "2"
      ]
    },
    {
      "subCategoryId": "",
      "subCategoryName": "",
      "storeListingIds": [
        "3","4","5"
      ]
    }
  ],
  "order": 2,
  "createdAt": {
    "$date": "2020-12-01T22:26:11.669Z"
  },
  "updatedAt": {
    "$date": "2021-04-27T17:17:25.442Z"
  },
  "_class": ""
}

入店

{
  "_id": "1",
  "storeListingName": "",
  "storeListingUrl": "",
  "catalogueIds": [
    ""
  ],
  "_class": ""
},
{
  "_id": "2",
  "storeListingName": "",
  "storeListingUrl": "",
  "catalogueIds": [
    ""
  ],
  "_class": ""
},
{
  "_id": "3",
  "storeListingName": "",
  "storeListingUrl": "",
  "catalogueIds": [
    ""
  ],
  "_class": ""
},
{
  "_id": "4",
  "storeListingName": "",
  "storeListingUrl": "",
  "catalogueIds": [
    ""
  ],
  "_class": ""
},
{
  "_id": "5",
  "storeListingName": "",
  "storeListingUrl": "",
  "catalogueIds": [
    ""
  ],
  "_class": ""
}

我需要在集合上方查找表单到 storeListing 集合。我也想要 storeListing 的所有字段。

结果应如下所示：

{
  "_id": "",
  "categoryName": "",
  "subCategories": [
    {
      "subCategoryId": "",
      "subCategoryName": "",
      "storeListingIds": [
        "1",
        "2"
      ],
      "storeListings":[
        {
          "_id": "1",
          "storeListingName": "",
          "storeListingUrl": "",
          "catalogueIds": [
            ""
          ],
          "_class": ""
        },
        {
          "_id": "2",
          "storeListingName": "",
          "storeListingUrl": "",
          "catalogueIds": [
            ""
          ],
          "_class": ""
        }
      ]
    },
    {
      "subCategoryId": "",
      "subCategoryName": "",
      "storeListingIds": [
        "3","4","5"
      ],
      "storeListings":[
        {
          "_id": "3",
          "storeListingName": "",
          "storeListingUrl": "",
          "catalogueIds": [
            ""
          ],
          "_class": ""
        },
        {
          "_id": "4",
          "storeListingName": "",
          "storeListingUrl": "",
          "catalogueIds": [
            ""
          ],
          "_class": ""
        },
        {
          "_id": "5",
          "storeListingName": "",
          "storeListingUrl": "",
          "catalogueIds": [
            ""
          ],
          "_class": ""
        }
      ]
    }
  ],
  "order": 2,
  "createdAt": {
    "$date": "2020-12-01T22:26:11.669Z"
  },
  "updatedAt": {
    "$date": "2021-04-27T17:17:25.442Z"
  },
  "_class": ""
}

标签： mongodbmongodb-queryaggregation-framework

解决方案

解释

  1. 我们执行$lookup嵌套storeListingId并将结果存储在tmp字段中
  2. 我们应用过滤并将subCategories对象与storeListings值合并

注意：我们使用的原因$mergeObjects是为了避免命名中的每个字段subCategories

$map: {
    input: "$subCategories",
    as: "subCat",
    in: {
        subCategoryId:"$$subCat.subCategoryId",
        subCategoryName:"$$subCat.subCategoryName",
        storeListingIds: "$$subCat.storeListingIds",
        storeListening: {
            $filter: {
                input: "$tmp",
                cond: {
                  $in: [ "$$this._id", "$$subCat.storeListingIds" ]
                }
            }
        }
    }
}

试试这个：

db.category.aggregate([
  {
    $lookup: {
      from: "storelisting",
      localField: "subCategories.storeListingIds",
      foreignField: "_id",
      as: "tmp"
    }
  },
  {
    $addFields: {
      tmp: "$$REMOVE",
      subCategories: {
        $map: {
          input: "$subCategories",
          as: "subCat",
          in: {
            "$mergeObjects": [
              "$$subCat",
              {
                storeListings: {
                  $filter: {
                    input: "$tmp",
                    cond: {
                      $in: [ "$$this._id", "$$subCat.storeListingIds" ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Mongo游乐场

推荐阅读