时间戳

首页 > 解决方案 > 在匹配阶段后将展开的数组分组并保留父数据/结构

mongodb - 在匹配阶段后将展开的数组分组并保留父数据/结构

问题描述

有一些问题让我的聚合工作。我有一个$lookup我加入两个集合的地方，在加入的一个中，我有嵌套数组，其中包含我想要匹配的对象。匹配部分似乎起作用了，因为我没有得到我在$group舞台上期望的结构之后$unwind。

这是一个带有示例数据的 mongo 游乐场https://mongoplayground.net/p/rWuX7ziT395

原始加入的文档具有这样的结构，其中我在一些approvalStatus 字段上匹配并且我得到正确的字段。但我也想保留“父”中的字段，即版本、allHaveSigned、isLatestVersion 等。

"agreements": [
    {
      "_id": {
        "$oid": "612c8d394459ebd2483a0920"
      },
      "agreementVersion": [
        {
          "version": 1,
          "allHaveSigned": false,
          "_id": {
            "$oid": "612c8d394459ebd2483a0921"
          },
          "approvalStatus": [
            {
              "_id": {
                "$oid": "612c8d394459ebd2483a0922"
              },
              "publisherSignsFor": {
                "$oid": "60801bef03a9cd5f4a097e76"
              },
              "signingPerson": {
                "$oid": "60d06fc53a7fc65be3787df8"
              },
              "isSignedByPublisher": true
            },
            {
              "_id": {
                "$oid": "612c8d394459ebd2483a0923"
              },
              "publisherSignsFor": null,
              "signingPerson": {
                "$oid": "60d06fc53a7fc65be3787df8"
              },
              "isSignedByPublisher": false
            },
            {
              "_id": {
                "$oid": "612c8d394459ebd2483a0921"
              },
              "publisherSignsFor": {
                "$oid": "60801bef03a9cd5f4a097e76"
              },
              "signingPerson": {
                "$oid": "60d06fc53a7fc65be3787df9"
              },
              "isSignedByPublisher": true
            }
          ],
          "isLatestVersion": true,
          "createdAt": {
            "$date": "2021-08-30T07:48:09.979Z"
          }
        }
      ],

这就是我的管道的样子

db.songs.aggregate([
  {
    "$lookup": {
      "from": "agreements",
      "localField": "agreements.songComposition",
      "foreignField": "_id",
      "as": "agreements.songComposition"
    }
  },
  {
    "$unwind": {
      "path": "$agreements.songComposition",
      "preserveNullAndEmptyArrays": false
    }
  },
  {
    "$unwind": {
      "path": "$agreements.songComposition.agreementVersion",
      "preserveNullAndEmptyArrays": false
    }
  },
  {
    "$unwind": {
      "path": "$agreements.songComposition.agreementVersion.approvalStatus",
      "preserveNullAndEmptyArrays": false
    }
  },
  {
    "$match": {
      "$and": [
        {
          "agreements.songComposition.agreementVersion.isLatestVersion": true
        },
        {
          "agreements.songComposition.agreementVersion.approvalStatus.signingPerson": ObjectId("60d06fc53a7fc65be3787df8")
        }
      ],
      "$or": [
        {
          "agreements.songComposition.agreementVersion.approvalStatus.isApprovedByUser": false
        },
        {
          "agreements.songComposition.agreementVersion.approvalStatus.isApprovedByUser": {
            "$exists": false
          }
        }
      ]
    }
  },
  {
    "$facet": {
      "songs": [
        {
          "$group": {
            "_id": "$_id",
            "title": {
              "$first": "$title"
            },
            "artistName": {
              "$first": "$artistName"
            },
            "agreements": {
              "$push": "$agreements.songComposition"
            },
            "count": {
              "$sum": 1
            }
          }
        }
      ],
      "countAgreements": [
        {
          "$group": {
            "_id": null,
            "count": {
              "$sum": 1
            }
          }
        }
      ]
    }
  },
  {
    "$project": {
      "songs": 1,
      "sumAgreements": {
        "$arrayElemAt": [
          "$countAgreements.count",
          0
        ]
      }
    }
  }
])

结果看起来像这样，我在agreements数组中得到了两个元素，它应该只有一个，我认为这是因为我无法approvalStatus正确加入字段或缺少其他内容。

[
  {
    "songs": [
      {
        "_id": ObjectId("60debdaae7593406b682e45d"),
        "agreements": [
          {
            "__v": 0,
            "_id": ObjectId("612c8d394459ebd2483a0920"),
            "addedAt": ISODate("2021-08-30T07:48:09.979Z"),
            "agreementVersion": {
              "_id": ObjectId("612c8d394459ebd2483a0921"),
              "allHaveSigned": false,
              "approvalStatus": {
                "_id": ObjectId("612c8d394459ebd2483a0922"),
                "isSignedByPublisher": true,
                "publisherSignsFor": ObjectId("60801bef03a9cd5f4a097e76"),
                "signingPerson": ObjectId("60d06fc53a7fc65be3787df8")
              },
              "createdAt": ISODate("2021-08-30T07:48:09.979Z"),
              "isLatestVersion": true,
              "version": 1
            }
          },
          {
            "__v": 0,
            "_id": ObjectId("612c8d394459ebd2483a0920"),
            "addedAt": ISODate("2021-08-30T07:48:09.979Z"),
            "agreementVersion": {
              "_id": ObjectId("612c8d394459ebd2483a0921"),
              "allHaveSigned": false,
              "approvalStatus": {
                "_id": ObjectId("612c8d394459ebd2483a0923"),
                "isSignedByPublisher": false,
                "publisherSignsFor": null,
                "signingPerson": ObjectId("60d06fc53a7fc65be3787df8")
              },
              "createdAt": ISODate("2021-08-30T07:48:09.979Z"),
              "isLatestVersion": true,
              "version": 1
            }
          }
        ],
        "artistName": "My test artisters",
        "count": 2,
        "title": "Branches"
      }
    ],
    "sumAgreements": 2
  }
]

我想要的结果看起来像这样

[
  {
    "songs": [
      {
        "_id": ObjectId("60debdaae7593406b682e45d"),
        "agreements": [
          {
            "__v": 0,
            "_id": ObjectId("612c8d394459ebd2483a0920"),
            "addedAt": ISODate("2021-08-30T07:48:09.979Z"),
            "agreementVersion": [
              {
              "_id": ObjectId("612c8d394459ebd2483a0921"),
              "allHaveSigned": false,
              "approvalStatus": [
                {
                "_id": ObjectId("612c8d394459ebd2483a0922"),
                "isSignedByPublisher": true,
                "publisherSignsFor": ObjectId("60801bef03a9cd5f4a097e76"),
                "signingPerson": ObjectId("60d06fc53a7fc65be3787df8")
              },
              {"_id": ObjectId("612c8d394459ebd2483a0923"),
                "isSignedByPublisher": false,
                "publisherSignsFor": null,
                "signingPerson": ObjectId("60d06fc53a7fc65be3787df8")
              },
               ],
              "createdAt": ISODate("2021-08-30T07:48:09.979Z"),
              "isLatestVersion": true,
              "version": 1
            }
            ]
          },
          
        ],
        "artistName": "My test artisters",
        "count": 2,
        "title": "Branches"
      }
    ],
    "sumAgreements": 2
  }
]

感谢您的帮助！

标签： mongodbaggregation-framework

解决方案

您可以$lookup与管道一起使用，

  • $lookup与协议并传递songComposition为id
  • $match id（健康）状况
  • $unwind解构agreementVersion数组
  • $unwind解构approvalStatus数组
  • $match您的approvalStatus房产条件
  • $group通过_idand agreementVersion，_id获取必填字段并构造approvalStatus数组
  • $group通过_id并构造agreementVersion数组
  • $group通过 null 并构造songs数组
db.songs.aggregate([
  {
    "$lookup": {
      "from": "agreements",
      "let": { "id": "$agreements.songComposition" },
      "pipeline": [
        { $match: { $expr: { $eq: ["$$id", "$_id"] } } },
        {
          $unwind: {
            "path": "$agreementVersion",
            "preserveNullAndEmptyArrays": false
          }
        },
        {
          $unwind: {
            "path": "$agreementVersion.approvalStatus",
            "preserveNullAndEmptyArrays": false
          }
        },
        {
          "$match": {
            "$and": [
              { "agreementVersion.isLatestVersion": true },
              { "agreementVersion.approvalStatus.signingPerson": ObjectId("60d06fc53a7fc65be3787df8") },
              {
                "$or": [
                  { "agreementVersion.approvalStatus.isApprovedByUser": false },
                  { "agreementVersion.approvalStatus.isApprovedByUser": { "$exists": false } }
                ]
              }
            ]
          }
        },
        {
          $group: {
            _id: {
              _id: "$_id",
              agv_id: "$agreementVersion._id"
            },
            addedAt: { $first: "$addedAt" },
            __v: { $first: "$__v" },
            version: { $first: "$agreementVersion.version" },
            allHaveSigned: { $first: "$agreementVersion.allHaveSigned" },
            isLatestVersion: { $first: "$agreementVersion.isLatestVersion" },
            createdAt: { $first: "$agreementVersion.createdAt" },
            approvalStatus: { $push: "$agreementVersion.approvalStatus" },
            count: { $sum: 1 }
          }
        },
        {
          $group: {
            _id: "$_id._id",
            addedAt: { $first: "$addedAt" },
            __v: { $first: "$__v" },
            agreementVersion: {
              $push: {
                _id: "$_id.agv_id",
                version: "$version",
                allHaveSigned: "$allHaveSigned",
                isLatestVersion: "$isLatestVersion",
                createdAt: "$createdAt",
                approvalStatus: "$approvalStatus",
                count: "$count"
              }
            },
            count: { $sum: "$count" }
          }
        }
      ],
      "as": "agreements"
    }
  },
  {
    $group: {
      _id: null,
      songs: { $push: "$$ROOT" },
      count: { $sum: { $sum: "$agreements.count" } }
    }
  }
])

操场

推荐阅读