将具有相同键的对象合并为一个。

huangapple go评论74阅读模式
英文:

MongoDB : - Merge object with same key into one

问题

我正在尝试将数组中具有相同日期但status键名不同的对象合并到一起。

我有两个集合,userscanteens

我正在尝试获取结果的查询,但无法弄清如何合并具有相同Date的对象。

输出

User.aggregate([
    { $sort: { workerId: 1 } },
    {
      $lookup: {
        from: "canteens",
        localField: "_id",
        foreignField: "employeeId",
        pipeline: [
          {
            $match: {
              Date: {
                $gte: new Date(fromDate),
                $lte: new Date(toDate),
              },
            },
          },
          {
            $project: {
              Date: 1,
              status: 1,
            },
          },
        ],
        as: "canteens",
      },
    },
    {
      $project: {
        _id: 1,
        workerId: 1,
        workerFirstName: 1,
        workerSurname: 1,
        workerDepartment: 1,
        workerDesignation: 1,
        locationName: 1,
        canteenData: "$canteens",
      },
    },
  ]);
[
  {
    "_id": "60e6fd3616dd663e84a925e2",
    "workerFirstName": "Firstaname",
    "workerSurname": "lastname",
    "workerId": "1",
    "locationName": "location",
    "workerDesignation": "designation",
    "workerDepartment": "department",
    "canteenData": [
      {
        "_id": "63b285b9e92eee614feb7be1",
        "status": "LUNCH",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b2db8db10c24487201e0a2",
        "status": "DINNER",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b39b247adbeb50bfbe3503",
        "status": "BREAK FAST",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b3d248c076184fb07ff2c4",
        "status": "LUNCH",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b42b8ccb57a4cb7af34015",
        "status": "DINNER",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b4ef71e038498fe6634506",
        "status": "BREAK FAST",
        "Date": "2023-01-04T00:00:00.000Z"
      }
    ]
  },
  {
    "_id": "60e6fd3616dd663e84a925e2",
    "workerFirstName": "Firstaname1",
    "workerSurname": "lastname1",
    "workerId": "2",
    "locationName": "location",
    "workerDesignation": "designation",
    "workerDepartment": "department",
    "canteenData": [
      {
        "_id": "63b285b9e92eee614feb7be1",
        "status": "LUNCH",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b2db8db10c24487201e0a2",
        "status": "DINNER",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b39b247adbeb50bfbe3503",
        "status": "BREAK FAST",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b3d248c076184fb07ff2c4",
        "status": "LUNCH",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b42b8ccb57a4cb7af34015",
        "status": "DINNER",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b4ef71e038498fe6634506",
        "status": "BREAK FAST",
        "Date": "2023-01-04T00:00:00.000Z"
      }
    ]
  }
]

我要尝试的输出

[
  {
    "_id": "60e6fd3616dd663e84a925e2",
    "workerFirstName": "Firstanem",
    "workerSurname": "lastname",
    "workerId": "1",
    "locationName": "location",
    "workerDesignation": "designation",
    "workerDepartment": "department",
    "canteenData": [
      {
        "_id": "63b285b9e92eee614feb7be1",
        "status1": "LUNCH",
        "status2": "DINNER",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b39b247adbeb50bfbe3503",
        "status1": "BREAK FAST",
        "status2": "LUNCH",
        "status3": "DINNER",
        "Date": "2023-01-03T00:00:00.000Z"
      },
      {
        "_id": "63b4ef71e038498fe6634506",
        "status1": "BREAK FAST",
        "Date": "2023-01-04T00:00:00.000Z"
      }
    ]
  },
  {
    "_id": "60e6fd3616dd663e84a925e2",
    "workerFirstName": "Firstanem1",
    "workerSurname": "lastname1",
    "workerId": "2",
    "locationName": "location",
    "workerDesignation": "designation",
    "workerDepartment": "department",
    "canteenData": [
      {
        "_id": "63b285b9e92eee614feb7be1",
        "status1": "LUNCH",
        "status2": "DINNER",
        "Date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b39b247adbeb50bfbe3503",
        "status1

<details>
<summary>英文:</summary>

I am trying to merge an object inside an array with the same date but with a different key name for the `status` key.

I have 2 collections `users` and `canteens`

The query I am trying to get the result but am not able to figure out how to merge the object with the same `Date`

**OUTPUT**

```javascript
User.aggregate([
    { $sort: { workerId: 1 } },
    {
      $lookup: {
        from: &quot;canteens&quot;,
        localField: &quot;_id&quot;,
        foreignField: &quot;employeeId&quot;,
        pipeline: [
          {
            $match: {
              Date: {
                $gte: new Date(fromDate),
                $lte: new Date(toDate),
              },
            },
          },
          {
            $project: {
              Date: 1,
              status: 1,
            },
          },
        ],
        as: &quot;canteens&quot;,
      },
    },
    {
      $project: {
        _id: 1,
        workerId: 1,
        workerFirstName: 1,
        workerSurname: 1,
        workerDepartment: 1,
        workerDesignation: 1,
        locationName: 1,
        canteenData: &quot;$canteens&quot;,
      },
    },
  ]);
[
  {
    &quot;_id&quot;: &quot;60e6fd3616dd663e84a925e2&quot;,
    &quot;workerFirstName&quot;: &quot;Firstaname&quot;,
    &quot;workerSurname&quot;: &quot;lastname&quot;,
    &quot;workerId&quot;: &quot;1&quot;,
    &quot;locationName&quot;: &quot;location&quot;,
    &quot;workerDesignation&quot;: &quot;designation&quot;,
    &quot;workerDepartment&quot;: &quot;department&quot;,
    &quot;canteenData&quot;: [
      {
        &quot;_id&quot;: &quot;63b285b9e92eee614feb7be1&quot;,
        &quot;status&quot;: &quot;LUNCH&quot;,
        &quot;Date&quot;: &quot;2023-01-02T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b2db8db10c24487201e0a2&quot;,
        &quot;status&quot;: &quot;DINNER&quot;,
        &quot;Date&quot;: &quot;2023-01-02T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b39b247adbeb50bfbe3503&quot;,
        &quot;status&quot;: &quot;BREAK FAST&quot;,
        &quot;Date&quot;: &quot;2023-01-03T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b3d248c076184fb07ff2c4&quot;,
        &quot;status&quot;: &quot;LUNCH&quot;,
        &quot;Date&quot;: &quot;2023-01-03T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b42b8ccb57a4cb7af34015&quot;,
        &quot;status&quot;: &quot;DINNER&quot;,
        &quot;Date&quot;: &quot;2023-01-03T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b4ef71e038498fe6634506&quot;,
        &quot;status&quot;: &quot;BREAK FAST&quot;,
        &quot;Date&quot;: &quot;2023-01-04T00:00:00.000Z&quot;
      }
    ]
  },
{
    &quot;_id&quot;: &quot;60e6fd3616dd663e84a925e2&quot;,
    &quot;workerFirstName&quot;: &quot;Firstaname1&quot;,
    &quot;workerSurname&quot;: &quot;lastname1&quot;,
    &quot;workerId&quot;: &quot;2&quot;,
    &quot;locationName&quot;: &quot;location&quot;,
    &quot;workerDesignation&quot;: &quot;designation&quot;,
    &quot;workerDepartment&quot;: &quot;department&quot;,
    &quot;canteenData&quot;: [
      {
        &quot;_id&quot;: &quot;63b285b9e92eee614feb7be1&quot;,
        &quot;status&quot;: &quot;LUNCH&quot;,
        &quot;Date&quot;: &quot;2023-01-02T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b2db8db10c24487201e0a2&quot;,
        &quot;status&quot;: &quot;DINNER&quot;,
        &quot;Date&quot;: &quot;2023-01-02T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b39b247adbeb50bfbe3503&quot;,
        &quot;status&quot;: &quot;BREAK FAST&quot;,
        &quot;Date&quot;: &quot;2023-01-03T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b3d248c076184fb07ff2c4&quot;,
        &quot;status&quot;: &quot;LUNCH&quot;,
        &quot;Date&quot;: &quot;2023-01-03T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b42b8ccb57a4cb7af34015&quot;,
        &quot;status&quot;: &quot;DINNER&quot;,
        &quot;Date&quot;: &quot;2023-01-03T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b4ef71e038498fe6634506&quot;,
        &quot;status&quot;: &quot;BREAK FAST&quot;,
        &quot;Date&quot;: &quot;2023-01-04T00:00:00.000Z&quot;
      }
    ]
  }
]

The output I am trying to get


[
  {
    &quot;_id&quot;: &quot;60e6fd3616dd663e84a925e2&quot;,
    &quot;workerFirstName&quot;: &quot;Firstanem&quot;,
    &quot;workerSurname&quot;: &quot;lastname&quot;,
    &quot;workerId&quot;: &quot;1&quot;,
    &quot;locationName&quot;: &quot;location&quot;,
    &quot;workerDesignation&quot;: &quot;designation&quot;,
    &quot;workerDepartment&quot;: &quot;department&quot;,
    &quot;canteenData&quot;: [
      {
        &quot;_id&quot;: &quot;63b285b9e92eee614feb7be1&quot;,
        &quot;status1&quot;: &quot;LUNCH&quot;,
        &quot;status2&quot;: &quot;DINNER&quot;,
        &quot;Date&quot;: &quot;2023-01-02T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b39b247adbeb50bfbe3503&quot;,
        &quot;status1&quot;: &quot;BREAK FAST&quot;,
        &quot;status2&quot;: &quot;LUNCH&quot;,
        &quot;status3&quot;: &quot;DINNER&quot;,
        &quot;Date&quot;: &quot;2023-01-03T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b4ef71e038498fe6634506&quot;,
        &quot;status1&quot;: &quot;BREAK FAST&quot;,
        &quot;Date&quot;: &quot;2023-01-04T00:00:00.000Z&quot;
      }
    ]
  },
{
    &quot;_id&quot;: &quot;60e6fd3616dd663e84a925e2&quot;,
    &quot;workerFirstName&quot;: &quot;Firstanem1&quot;,
    &quot;workerSurname&quot;: &quot;lastname1&quot;,
    &quot;workerId&quot;: &quot;2&quot;,
    &quot;locationName&quot;: &quot;location&quot;,
    &quot;workerDesignation&quot;: &quot;designation&quot;,
    &quot;workerDepartment&quot;: &quot;department&quot;,
    &quot;canteenData&quot;: [
      {
        &quot;_id&quot;: &quot;63b285b9e92eee614feb7be1&quot;,
        &quot;status1&quot;: &quot;LUNCH&quot;,
        &quot;status2&quot;: &quot;DINNER&quot;,
        &quot;Date&quot;: &quot;2023-01-02T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b39b247adbeb50bfbe3503&quot;,
        &quot;status1&quot;: &quot;BREAK FAST&quot;,
        &quot;status2&quot;: &quot;LUNCH&quot;,
        &quot;status3&quot;: &quot;DINNER&quot;,
        &quot;Date&quot;: &quot;2023-01-03T00:00:00.000Z&quot;
      },
      {
        &quot;_id&quot;: &quot;63b4ef71e038498fe6634506&quot;,
        &quot;status1&quot;: &quot;BREAK FAST&quot;,
        &quot;Date&quot;: &quot;2023-01-04T00:00:00.000Z&quot;
      }
    ]
  }
]

答案1

得分: 1

一个选项是在您的$lookup管道聚合中添加2个步骤:

{
  "$group": {
    "_id": "$Date",
    "_idVal": { "$first": "$_id" },
    "data": { "$addToSet": "$status" }
  }
},
{
  "$replaceRoot": {
    "newRoot": {
      "$mergeObjects": [
        { "_id": "$_idVal", "Date": "$_id" },
        {
          "$arrayToObject": {
            "$reduce": {
              "input": "$data",
              "initialValue": [],
              "in": {
                "$concatArrays": [
                  "$$value",
                  [
                    {
                      "k": {
                        "$concat": [
                          "status",
                          {
                            "$toString": {
                              "$add": [
                                { "$size": "$$value" },
                                1
                              ]
                            }
                          }
                        ]
                      },
                      "v": "$$this"
                    }
                  ]
                ]
              }
            }
          }
        }
      ]
    }
  }
}

playground示例上查看它的运行方式。

英文:

One option is to add 2 steps into your $lookup pipeline aggregation:

  {$group: {
_id: &quot;$Date&quot;,
_idVal: {$first: &quot;$_id&quot;},
data: {$addToSet: &quot;$status&quot;}
}},
{$replaceRoot: {
newRoot: {
$mergeObjects: [
{_id: &quot;$_idVal&quot;, Date: &quot;$_id&quot;},
{$arrayToObject: {
$reduce: {
input: &quot;$data&quot;,
initialValue: [],
in: {$concatArrays: [
&quot;$$value&quot;,
[{k: {$concat: [
&quot;status&quot;,
{$toString: {$add: [{$size: &quot;$$value&quot;}, 1]}}
]},
v: &quot;$$this&quot;}]
]}
}
}}
]
}
}}

See how it works on the playground example

答案2

得分: 0

不容易动态创建status1status2,...变量,我们如何知道BREAK FAST应该是status1而不是status2

替代解决方案: 我们在相关子查询内使用$group,将所有status值推送到一个array中。

db.users.aggregate([
  {
    "$lookup": {
      "from": "canteens",
      "localField": "_id",
      "foreignField": "employeeId",
      "pipeline": [
        {
          // 在此处放置您的自定义过滤器
          $match: {}
        },
        {
          $group: {
            _id: "$Date",
            // 选择“first” canteens _id
            id: {
              $first: "$_id"
            },
            status: {
              $push: "$status"
            }
          }
        },
        {
          $project: {
            _id: "$id",
            Date: "$_id",
            status: 1
          }
        },
        
      ],
      "as": "canteenData",
    }
  }
])

MongoPlayground

英文:

It's not easy to create status1, status2, ... variables dynamically + how do we know BREAK FAST should be status1 and not status2.

Alternative solution: We $group inside correlated subqueries and push all status values into an array

db.users.aggregate([
{
&quot;$lookup&quot;: {
&quot;from&quot;: &quot;canteens&quot;,
&quot;localField&quot;: &quot;_id&quot;,
&quot;foreignField&quot;: &quot;employeeId&quot;,
pipeline: [
{
// Put your custom filters here
$match: {}
},
{
$group: {
_id: &quot;$Date&quot;,
//pick &quot;first&quot; canteens _id
id: {
$first: &quot;$_id&quot;
},
status: {
$push: &quot;$status&quot;
}
}
},
{
$project: {
_id: &quot;$id&quot;,
Date: &quot;$_id&quot;,
status: 1
}
},
],
as: &quot;canteenData&quot;,
}
}
])

MongoPlayground

huangapple
  • 本文由 发表于 2023年1月9日 00:22:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/75049421.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定