mongodb - 使用投影过滤嵌套数组而不使用展开

Question

我有我的 collection1，它在 projects 字段中包含 collection2 的 _id，如下所示:

    {
        "name": "adafd",
        "employeeId": "employeeId",
        "locations": [
            "ObjectId(adfaldjf)",
            "ObjectId(adfaldjf)",
            "ObjectId(adfaldjf)",
            "ObjectId(adfaldjf)",
            "ObjectId(adfaldjf)",
            "ObjectId(adfaldjf)"
        ]
    }

collection2如下

"collection2": [
    {   
        "location": "india",
        "states": [
            {
                "stateCode": "TN",
                    "districts": {
                        "cities": [
                            {
                                "code": 1,
                                "name": "xxx"
                            },
                            {
                                "code": 4,
                                "name": "zzz"
                            },
                            {
                                "code": 6,
                                "name": "yyy"
                            }
                        ]
                    }
            }
        ]
    }
]

我试图在查找后过滤 collection2 中的嵌套数组，如下所示:

    db.collection.aggregate([
        {
            $lookup: {
                from: "collection2",
                localField: "locations",
                foreignField: "_id",
                as: "locations"
            }
        },
        {
            $match: {
                "name": "adafd",
            },
        },
        {
            $project: {
                'details': {
                    $filter: {
                        input: "$locations",
                        as: "location",
                        cond: { 
                            "$eq": ["$$location.states.stateCode", "TN" ]
                        }
                    }
                }
            }
        }
    ]
)

它为 locations 返回一个空数组。

我按如下方式修改项目以在投影中甚至过滤 collection2 数组内的状态，但过滤器不适用。它返回 states 数组中的所有数据。

{
        $project: {
            'details': {
                $filter: {
                    input: "$locations",
                    as: "location",
                    cond: { 
                        $filter: {
                            input: "$location.states",
                            as: "state",
                            cond: { 
                                "$eq": ["$$state.stateCode", "TN" ]
                            }
                        }
                    }
                }
            }
        }
    }

我已经找到了几个与此相关的解决方案，但没有一个对我有用。因为我不想使用放松。有什么办法可以实现这一点..？

注意:我不想在 $lookup 中使用 pipeline，因为 DocumentDB 不支持它。查询中还应该有任何 $unwind 和 $group。

Answer 1

• $match 您的条件
• $lookup 与 collection2
• $project 按位置名称过滤 locations
• $unwind 解构locations数组
• $project 按状态码过滤状态
• $unwind 解构states数组
• $project 按城市代码过滤城市
• $unwind 解构cities数组

db.collection1.aggregate([
  { $match: { name: "adafd" } },
  {
    $lookup: {
      from: "collection2",
      localField: "locations",
      foreignField: "_id",
      as: "locations"
    }
  },
  {
    $project: {
      locations: {
        $filter: {
          input: "$locations",
          cond: { $eq: ["$$this.location", "india"] }
        }
      }
    }
  },
  { $unwind: "$locations" },
  {
    $project: {
      locations: {
        _id: "$locations._id",
        location: "$locations.location",
        states: {
          $filter: {
            input: "$locations.states",
            cond: { $eq: ["$$this.stateCode", "TN"] }
          }
        }
      }
    }
  },
  { $unwind: "$locations.states" },
  {
    $project: {
      locations: {
        _id: "$locations._id",
        location: "$locations.location",
        states: {
          stateCode: "$locations.states.stateCode",
          districts: {
            cities: {
              $filter: {
                input: "$locations.states.districts.cities",
                cond: { $eq: ["$$this.code", 1] }
              }
            }
          }
        }
      }
    }
  },
  { $unwind: "$locations.states.districts.cities" }
])

Playground

---

不使用 $unwind 的第二个选项，而不是你可以使用 $arrayElemAt，

db.collection1.aggregate([
  { $match: { name: "adafd" } },
  {
    $lookup: {
      from: "collection2",
      localField: "locations",
      foreignField: "_id",
      as: "locations"
    }
  },
  {
    $project: {
      locations: {
        $arrayElemAt: [
          {
            $filter: {
              input: "$locations",
              cond: { $eq: ["$$this.location", "india"] }
            }
          },
          0
        ]
      }
    }
  },
  {
    $project: {
      locations: {
        _id: "$locations._id",
        location: "$locations.location",
        states: {
          $arrayElemAt: [
            {
              $filter: {
                input: "$locations.states",
                cond: { $eq: ["$$this.stateCode", "TN"] }
              }
            },
            0
          ]
        }
      }
    }
  },
  {
    $project: {
      locations: {
        _id: "$locations._id",
        location: "$locations.location",
        states: {
          stateCode: "$locations.states.stateCode",
          districts: {
            cities: {
              $arrayElemAt: [
                {
                  $filter: {
                    input: "$locations.states.districts.cities",
                    cond: { $eq: ["$$this.code", 1] }
                  }
                },
                0
              ]
            }
          }
        }
      }
    }
  }
])

Playground