MongoDB：- $对象内部的查找数组

bvjxkvbb 于 2022-11-22 发布在 Go

关注(0)|答案(2)|浏览(138)

我有3个集合用户、出席、检测
用户收款单据

{
      "_id": "60dd781d4524e6c116e234d2",
      "workerFirstName": "AMIT",
      "workerSurname": "SHAH",
      "workerId": "1001",
      "locationName": "HEAD OFFICE",
      "workerDesignation": "IT",
      "workerDepartment": "IT",
    },

考勤单据

{
        "_id": "61307cee85b5055a15cf01b7",
        "employeeId":"60dd781d4524e6c116e234d2",
        "Date": "2022-11-01T00:00:00.000Z",
        "duration": null,
        "createdAs": "FULL-DAY",
        "detections": [
          "636095dc00d9abc953d8fd57",
          "636132e6bf6fe52c582853b3"
        ]
      }

检测文件

{
  "_id": "636095dc00d9abc953d8fd57",
  "AttendanceId": "61307cee85b5055a15cf01b7"
},
{
  "_id": "636132e6bf6fe52c582853b3",
  "AttendanceId": "61307cee85b5055a15cf01b7"
}

获取所有用户$查找到的出勤情况，并获取与用户相关的所有出勤情况。
在考勤对象中还有一个检测数组，我还想填充考勤对象中的检测数组。
我尝试的查询

const dailyAttendance = await User.aggregate([
      { $sort: { workerId: 1 } },
      {
        $match: {
          lastLocationId: Mongoose.Types.ObjectId(locationId),
          workerType: workerType,
          isActive: true,
          workerId: {
            $nin: [
              "8080",
              "9999",
              "9998",
              "9997",
              "9996",
              "9995",
              "9994",
            ],
          },
        },
      },
      {
        $project: {
          _id: 1,
          workerId: 1,
          workerFirstName: 1,
          workerSurname: 1,
          workerDepartment: 1,
          workerDesignation: 1,
          locationName: 1,
        },
      },
      {
        $lookup: {
          from: "attendances",
          localField: "_id",
          foreignField: "employeeId",
          pipeline: [
            {
              $match: {
                Date: new Date(date),
              },
            },
            {
              $project: typesOfData,
            },
          ],
          as: "attendances",
        },
      },
      {
        $unwind: {
          path: "$attendances",
          preserveNullAndEmptyArrays: true,
        },
      },
    ])

我得到的输出

{
  "dailyAttendance": [
    {
      "_id": "60dd781d4524e6c116e234d2",
      "workerFirstName": "AMIT",
      "workerSurname": "SHAH",
      "workerId": "1001",
      "locationName": "HEAD OFFICE",
      "workerDesignation": "IT",
      "workerDepartment": "IT",
      "attendances": {
        "_id": "61307cee85b5055a15cf01b7",
        "Date": "2022-11-01T00:00:00.000Z",
        "duration": null,
        "createdAs": "FULL-DAY",
        "detections": [
          "636095dc00d9abc953d8fd57",
          "636132e6bf6fe52c582853b3"
        ]
      }
    },
    {
      "_id": "60dd781c4524e6c116e2336c",
      "workerFirstName": "MADASWAMY",
      "workerSurname": "KARUPPASWAMY",
      "workerId": "1002",
      "locationName": "HEAD OFFICE",
      "workerDesignation": "IT",
      "workerDepartment": "IT",
      "attendances": {
        "_id": "61307ce485b5055a15ceec02",
        "Date": "2022-11-01T00:00:00.000Z",
        "duration": null,
        "createdAs": "FULL-DAY",
        "detections": [
          "636095dc00d9abc953d8fd57",
          "636132e6bf6fe52c582853b3"
        ]
      }
    }
  ]
}

mongodb

来源：https://stackoverflow.com/questions/74442280/mongodb-lookup-array-inside-object

2条答案

按热度按时间

zpjtge221#

您可以尝试以下操作：

db.user.aggregate([
  {
    "$lookup": {
      "from": "attendance",
      "let": {
        userId: "$_id"
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              "$eq": [
                "$$userId",
                "$employeeId"
              ]
            }
          }
        },
        {
          "$unwind": {
            path: "$detections",
            preserveNullAndEmptyArrays: true
          }
        },
        {
          "$lookup": {
            "from": "detection",
            "localField": "detections",
            "foreignField": "_id",
            "as": "detections"
          }
        },
        {
          "$unwind": {
            path: "$detections",
            preserveNullAndEmptyArrays: true
          }
        },
        {
          "$group": {
            "_id": {
              "Date": "$Date",
              "_id": "$_id",
              "createdAs": "$createdAs",
              "duration": "$duration",
              "employeeId": "$employeeId"
            },
            "detections": {
              "$push": "$detections"
            }
          }
        },
        {
          "$replaceRoot": {
            "newRoot": {
              "$mergeObjects": [
                "$_id",
                {
                  detections: "$detections"
                }
              ]
            }
          }
        }
      ],
      "as": "attendance"
    }
  }
])

Playground link.

赞(0）回复(0）举报 2022-11-22

bksxznpy2#

您必须首先展开$attandances.detections，然后从检测文档中查找attandances.detections作为locafield，_id作为foreignField

const dailyAttendance = await User.aggregate([
            { $sort: { workerId: 1 } },
            {
                $match: {
                    lastLocationId: Mongoose.Types.ObjectId(locationId),
                    workerType: workerType,
                    isActive: true,
                    workerId: {
                        $nin: [
                            "8080",
                            "9999",
                            "9998",
                            "9997",
                            "9996",
                            "9995",
                            "9994",
                        ],
                    },
                },
            },
            {
                $project: {
                    _id: 1,
                    workerId: 1,
                    workerFirstName: 1,
                    workerSurname: 1,
                    workerDepartment: 1,
                    workerDesignation: 1,
                    locationName: 1,
                },
            },
            {
                $lookup: {
                    from: "attendances",
                    localField: "_id",
                    foreignField: "employeeId",
                    pipeline: [
                        {
                            $match: {
                                Date: new Date(date),
                            },
                        },
                        {
                            $project: typesOfData,
                        },
                    ],
                    as: "attendances",
                },
            },
            {
                $unwind: {
                    path: "$attendances",
                    preserveNullAndEmptyArrays: true,
                },
            },
            {
                '$unwind': {
                 path: '$attandances.detections', 
                 preserveNullAndEmptyArrays: true
                }
            },
            {
                $lookup: {
                    from: "detections",
                    localField: "attandances.detections",
                    foreignField: "_id",
                    as: "occurances",
                },
            },
        ])

您将获得以下输出x1c 0d1xx 1c 1d 1xx 1c 2d 1x

赞(0）回复(0）举报 2022-11-22

我来回答

MongoDB：- $对象内部的查找数组

2条答案

相关问题

热门标签

最新问答