Mongodb子文档数组查找到子文档数组

Question

我是新来的mongo。我有以下收藏。

文件：

{
    "_id" : ObjectId("5eb256c9e519051af2eeb5f7"),
    "name" : "Income",
    "types" : [ 
        {
            "typeId" : ObjectId("5eb257a3e519051af2eeb624"),
            "name" : "W2",
            "tenantId" : null,
            "message" : "",
            "createdOn" : null,
            "createdBy" : 1.0,
            "isActive" : true
        }, 
        {
            "typeId" : ObjectId("5eb257a3e519051af2eeb639"),
            "name" : "Salary Slip",
            "tenantId" : 1,
            "message" : "",
            "createdOn" : null,
            "createdBy" : 1.0,
            "isActive" : true
        }
    ]
}

请求：

{
    "_id" : ObjectId("5eb25d1fe519051af2eeb72d"),
    "employeeId" : 1234,
    "customerId" : 1275,
    "tenantId" : 1,
    "createdOn" : ISODate("2013-10-01T00:00:00.000Z"),
    "loanApplicationId" : 1.0,
    "status" : "requested",
    "message" : "Dear John, please send following documents.",
    "documents" : [ 
        {
            "typeId" : null,
            "displayName" : "W2 2016",
            "message" : "please upload salary slip for year 2016",
            "status" : "requested",
            "files" : []
        }, 
        {
            "typeId" : ObjectId("5eb257a3e519051af2eeb624"),
            "displayName" : "W2 2016",
            "message" : "please upload salary slip for year 2016",
            "status" : "requested",
            "files" : []
        }
    ]
}

文档集合中的typeId是文档类型的id，其中请求中的typeId是也可以为空的外部字段。如何获得以下输出。

{
    "_id" : ObjectId("5eb25d1fe519051af2eeb72d"),
    "employeeId" : 1234,
    "customerId" : 1275,
    "tenantId" : 1,
    "createdOn" : ISODate("2013-10-01T00:00:00.000Z"),
    "loanApplicationId" : 1.0,
    "status" : "requested",
    "message" : "Dear John, please send following documents.",
    "documents" : [ 
        {
            "typeId" : null,
            "typeInfo": null,
            "displayName" : "W2 2016",
            "message" : "please upload salary slip for year 2016",
            "status" : "requested",
            "files" : []
        }, 
        {
            "typeId" : ObjectId("5eb257a3e519051af2eeb624"),
            "typeInfo": {
            "name": "W2" 
            },
            "displayName" : "W2 2016",
            "message" : "please upload salary slip for year 2016",
            "status" : "requested",
            "files" : []
        }
    ]
}

Answer 1

使用默认的$查找语法，可以使用原子/数组值进行联接，并将结果存储在tmp变量中。在下一步中，我们迭代文档字段，并在tmp中找到具有相同类型ID的变量项。

注意：如果您需要的字段多于名称，只需更改为类型信息：“$$this”}。

试试这个：

db.Requests.aggregate([
  {
    $lookup: {
      from: "Documents",
      localField: "documents.typeId",
      foreignField: "types.typeId",
      as: "tmp"
    }
  },
  {
    $addFields: {
      tmp: "$$REMOVE",
      documents: {
        $map: {
          input: "$documents",
          as: "doc",
          in: {
            $mergeObjects: [
              "$$doc",
              {
                $reduce: {
                  input: {
                    $reduce: {
                      input: "$tmp.types",
                      initialValue: [],
                      in: {
                        $concatArrays: [
                          "$$value",
                          "$$this"
                        ]
                      }
                    }
                  },
                  initialValue: {},
                  in: {
                    $cond: [
                      {
                        $eq: [
                          "$$this.typeId",
                          "$$doc.typeId"
                        ]
                      },
                      {
                        typeInfo: {
                          name: "$$this.name"
                        }
                      },
                      "$$value"
                    ]
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

MongoPlayground

Answer 2

我喜欢解决这个问题，这是一个棘手、漫长而复杂的问题。嗯，我也是初学者，所以我使用了所有的基本概念。您可以尝试以下代码：

db.request.aggregate([
  {
    $unwind: "$documents"
  },
  {
    $lookup: {
      from: "document",
      let: {
        req_typeId: "$documents.typeId"
      },
      pipeline: [
        {
          $unwind: "$types"
        },
        {
          $match: {
            $expr: {
              $eq: [
                "$$req_typeId",
                "$types.typeId"
              ]
            }
          }
        }
      ],
      as: "documents.example"
    }
  },
  {
    $unwind: {
      path: "$documents.example",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: "$_id",
      employeeId: {
        $first: "$employeeId"
      },
      customerId: {
        $first: "$customerId"
      },
      tenantId: {
        $first: "$tenantId"
      },
      createdOn: {
        $first: "$createdOn"
      },
      loanApplicationId: {
        $first: "$loanApplicationId"
      },
      status: {
        $first: "$status"
      },
      message: {
        $first: "$message"
      },
      documents: {
        $push: {
          typeId: "$documents.typeId",
          typeInfo: {
            $cond: [
              {
                $eq: [
                  "$documents.example",
                  undefined
                ]
              },
              null,
              {
                name: "$documents.example.types.name"
              }
            ]
          },
          displayName: "$documents.displayName",
          message: "$documents.message",
          status: "$documents.status",
          files: "$documents.files"
        }
      }
    }
  }
])

如果您在任何时候遇到困难，我已经提供了MongoPlayGround链接，您可以在这里一步一步地执行聚合管道的查询，否则请联系我。这是一个很长的问题，需要时间才能理解。同时，我会尽量缩小它。如果我得到什么，我会在这里更新。

编辑：

我将查询缩短了近50%（按行计算）。这是我以前的方法的链接