mongoose Mongodb聚合：-从$lookup嵌套数组中获取特定字段

dluptydi 于 2022-11-13 发布在 Go

关注(0)|答案(3)|浏览(260)

我尝试从aggregate、lookup和一些cond之后得到的数组中获取特定字段
下面你可以看到我的查询

const attendanceData = await User.aggregate([
    {
      $match: {
        lastLocationId: Mongoose.Types.ObjectId(typeId),
        isActive: true,
      },
    },
    {
      $project: {
        _id: 1,
        workerId: 1,
        workerFirstName: 1,
        workerSurname: 1,
      },
    },
    {
      $lookup: {
        from: "attendances",
        localField: "_id",
        foreignField: "employeeId",
        as: "attendances",
      },
    },
    {
      $set: {
        attendances: {
          $filter: {
            input: "$attendances",
            cond: {
              $and: [
                {
                  $gte: ["$$this.Date", new Date(fromDate)],
                },
                {
                  $lte: ["$$this.Date", new Date(toDate)],
                },
                {
                  $eq: ["$$this.createdAs", dataType],
                },
                {
                  $eq: ["$$this.status", true],
                },
                {
                  $eq: ["$$this.workerType", workerType],
                },
              ],
            },
          },
        },
      },
    },
    { $skip: 0 },
    { $limit: 10 },
  ]);

下面是我得到的响应数据

{
  "attendanceSheet": [
    {
      "_id": "60dd77c14524e6c116e16aaa",
      "workerFirstName": "MEGHRAJ",
      "workerSurname": "JADHAV",
      "workerId": "2036",
      "attendances": [
        {
          "_id": "6130781085b5055a15c32f2u",
          "workerId": "2036",
          "workerFullName": "MEGHRAJ JADHAV",
          "workerType": "Employee",
          "Date": "2022-10-01T00:00:00.000Z",
          "createdAs": "ABSENT"
        },
        {
          "_id": "6130781085b5055a15c32f2u",
          "workerId": "2036",
          "workerFullName": "MEGHRAJ JADHAV",
          "workerType": "Employee",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    },
    {
      "_id": "60dd77c24524e6c116e16c0f",
      "workerFirstName": "SANJAY",
      "workerSurname": "DUTTA",
      "workerId": "2031",
      "attendances": [
        {
          "_id": "6130781a85b5055a15c3455y",
          "workerId": "2031",
          "workerFullName": "SANJAY DUTTA",
          "workerType": "Employee",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    }
  ]
}

但我希望数据像下面这样只有几个字段在不是每个字段

{
  "attendanceSheet": [
    {
      "_id": "60dd77c14524e6c116e16aaa",
      "workerFirstName": "MEGHRAJ",
      "workerSurname": "JADHAV",
      "workerId": "2036",
      "attendances": [
        {
          "_id": "6130781085b5055a15c32f2u",
          "Date": "2022-10-01T00:00:00.000Z",
          "createdAs": "ABSENT"
        },
        {
          "_id": "6130781085b5055a15c32f2u",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    },
    {
      "_id": "60dd77c24524e6c116e16c0f",
      "workerFirstName": "SANJAY",
      "workerSurname": "DUTTA",
      "workerId": "2031",
      "attendances": [
        {
          "_id": "6130781a85b5055a15c3455y",
          "Date": "2022-10-02T00:00:00.000Z",
          "createdAs": "ABSENT"
        }
      ]
    }
  ]
}

mongoose

来源：https://stackoverflow.com/questions/74358793/mongodb-aggregation-get-specific-fields-from-lookup-nested-array

3条答案

按热度按时间

s5a0g9ez1#

您可以通过将所有匹配放在"$lookup""pipeline"中来简化/重构您的聚合管道。

db.users.aggregate([
  {
    "$match": {
      "lastLocationId": ObjectId("0123456789abcdef01234567"),
      "isActive": true
    }
  },
  {
    "$project": {
      "workerId": 1,
      "workerFirstName": 1,
      "workerSurname": 1
    }
  },
  {
    "$lookup": {
      "from": "attendances",
      "localField": "_id",
      "foreignField": "employeeId",
      "as": "attendances",
      // do all the matching here
      "pipeline": [
        {
          "$match": {
            "Date": {
              // fromDate, toDate
              "$gte": ISODate("2022-09-01T00:00:00Z"),
              "$lte": ISODate("2022-09-30T23:59:59Z")
            },
            // dataType
            "createdAs": "ABSENT",
            "status": true,
            // workerType
            "workerType": "Employee"
          }
        },
        {
          "$project": {
            "Date": 1,
            "createdAs": 1
          }
        }
      ]
    }
  },
  {$skip: 0},
  {$limit: 10}
])

在mongoplayground.net上试试。

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

r7knjye22#

从您所拥有的内容获取所请求的输出的一个选项是$map和$reduce：

db.collection.aggregate([
  {
    $set: {
      attendanceSheet: {
        $map: {
          input: "$attendanceSheet",
          as: "external",
          in: {
            $mergeObjects: [
              "$$external",
              {
                attendances: {
                  $reduce: {
                    input: "$$external.attendances",
                    initialValue: [],
                    in: {
                      $concatArrays: [
                        "$$value",
                        [
                          {
                            _id: "$$this._id",
                            createdAs: "$$this.createdAs",
                            Date: "$$this.Date"
                          }
                        ]
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

了解它在playground example上的工作原理

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

zbdgwd5y3#

下面的修改对我很有效

const attendanceData = await User.aggregate([
    {
      $match: {
        lastLocationId: Mongoose.Types.ObjectId(typeId),
        isActive: true,
      },
    },
    {
      $project: {
        _id: 1,
        workerId: 1,
        workerFirstName: 1,
        workerSurname: 1,
      },
    },
    {
      $lookup: {
        from: "attendances",
        localField: "_id",
        foreignField: "employeeId",
        as: "attendances",
      },
    },
    {
      $set: {
        attendances: {
          $filter: {
            input: "$attendances",
            cond: {
              $and: [
                {
                  $gte: ["$$this.Date", new Date(fromDate)],
                },
                {
                  $lte: ["$$this.Date", new Date(toDate)],
                },
                {
                  $eq: ["$$this.createdAs", dataType],
                },
                {
                  $eq: ["$$this.status", true],
                },
                {
                  $eq: ["$$this.workerType", workerType],
                },
              ],
            },
          },
        },
      },
    },
    {
      $set: {
        attendances: {
          $reduce: {
            input: "$attendances",
            initialValue: [],
            in: {
              $concatArrays: [
                "$$value",
                [
                  {
                    _id: "$$this._id",
                    createdAs: "$$this.createdAs",
                    Date: "$$this.Date",
                  },
                ],
              ],
            },
          },
        },
      },
    },
    { $skip: 0 },
    { $limit: 10 },
  ]);

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

我来回答

mongoose Mongodb聚合：-从$lookup嵌套数组中获取特定字段

3条答案

相关问题

热门标签

最新问答