如何使用MongoDB聚合查找数组中每个对象的特定字段。

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

How to lookup a specific field of every object of an array using mongodb aggregation

问题

我试图查找rentalcontractdetails表中的每个rentalContractId。产品文档看起来像这样:

{
  "productPrice": [
    {
      "rentalContractId": ObjectId("64018dfc8a7d37f378d6c427"),
      "price": 20
    },
    {
      "rentalContractId": ObjectId("64018dfc8a7d37f378d6c426"),
      "price": 10
    }
  ]
}

我只需要查找rentalContractId。因此,在聚合中进行了以下查询以获得该输出:

    db.products.aggregate([
    {
          $lookup: {
            from: "rentalcontractdetails",
            localField: "productPrice.rentalContractId",
            foreignField: "_id",
            as: "rentalContract",
          },
        }, { $set: {
          "productPrice.rentalContract": "$rentalContract"
        }}])

但这产生了这样的输出。

"productPrice":

[
                    {
                        "rentalContractId": "64018dfc8a7d37f378d6c427",
                        "price": 20,
                        "rentalContract": [
                            {
                                "_id": "64018dfc8a7d37f378d6c427",
                                "contractType": "8 - Days Rental",
                                "contractDaysInNumber": 8,
                                "createdAt": "2023-03-03T06:04:44.531Z",
                                "isDeleted": false,
                                "isArchived": false
                            },
                            {
                                "_id": "64018dfc8a7d37f378d6c426",
                                "contractType": "4 - Days Rental",
                                "contractDaysInNumber": 4,
                                "createdAt": "2023-03-03T06:04:44.531Z",
                                "isDeleted": false,
                                "isArchived": false
                            }
                        ]
                    },
                    {
                        "rentalContractId": "64018dfc8a7d37f378d6c426",
                        "price": 10,
                        "rentalContract": [
                            {
                                "_id": "64018dfc8a7d37f378d6c427",
                                "contractType": "8 - Days Rental",
                                "contractDaysInNumber": 8,
                                "createdAt": "2023-03-03T06:04:44.531Z",
                                "isDeleted": false,
                                "isArchived": false
                            },
                            {
                                "_id": "64018dfc8a7d37f378d6c426",
                                "contractType": "4 - Days Rental",
                                "contractDaysInNumber": 4,
                                "createdAt": "2023-03-03T06:04:44.531Z",
                                "isDeleted": false,
                                "isArchived": false
                            }
                        ]
                    }
                ]

预期输出:

    {
      "productPrice": [
        {
          "rentalContractId": {
              "_id": "64018dfc8a7d37f378d6c427",
              "contractType": "8 - Days Rental",
            },
          "price": 20
        },
        {
          "rentalContractId": {
              "_id": "64018dfc8a7d37f378d6c426",
              "contractType": "4 - Days Rental",
            }
          "price": 10
        }
      ]
    }
英文:

I'm trying to lookup every rentalContractId from rentalcontractdetails table. The product document looks something like this:

{
  "productPrice": [
    {
      "rentalContractId": ObjectId("64018dfc8a7d37f378d6c427"),
      "price": 20
    },
    {
      "rentalContractId": ObjectId("64018dfc8a7d37f378d6c426"),
      "price": 10
    }
  ]
}

I just need to lookup rentalContractId. So was doing the below query in aggregation to get that output,

db.products.aggregate([
{
      $lookup: {
        from: "rentalcontractdetails",
        localField: "productPrice.rentalContractId",
        foreignField: "_id",
        as: "rentalContract",
      },
    }, { $set: {
      "productPrice.rentalContract": "$rentalContract"
    }}])

but this is producing output like this.
"productPrice":

[
                    {
                        "rentalContractId": "64018dfc8a7d37f378d6c427",
                        "price": 20,
                        "rentalContract": [
                            {
                                "_id": "64018dfc8a7d37f378d6c427",
                                "contractType": "8 - Days Rental",
                                "contractDaysInNumber": 8,
                                "createdAt": "2023-03-03T06:04:44.531Z",
                                "isDeleted": false,
                                "isArchived": false
                            },
                            {
                                "_id": "64018dfc8a7d37f378d6c426",
                                "contractType": "4 - Days Rental",
                                "contractDaysInNumber": 4,
                                "createdAt": "2023-03-03T06:04:44.531Z",
                                "isDeleted": false,
                                "isArchived": false
                            }
                        ]
                    },
                    {
                        "rentalContractId": "64018dfc8a7d37f378d6c426",
                        "price": 10,
                        "rentalContract": [
                            {
                                "_id": "64018dfc8a7d37f378d6c427",
                                "contractType": "8 - Days Rental",
                                "contractDaysInNumber": 8,
                                "createdAt": "2023-03-03T06:04:44.531Z",
                                "isDeleted": false,
                                "isArchived": false
                            },
                            {
                                "_id": "64018dfc8a7d37f378d6c426",
                                "contractType": "4 - Days Rental",
                                "contractDaysInNumber": 4,
                                "createdAt": "2023-03-03T06:04:44.531Z",
                                "isDeleted": false,
                                "isArchived": false
                            }
                        ]
                    }
                ]

expected output:

{
  "productPrice": [
    {
      "rentalContractId": {
          "_id": "64018dfc8a7d37f378d6c427",
          "contractType": "8 - Days Rental",
        },
      "price": 20
    },
    {
      "rentalContractId": {
          "_id": "64018dfc8a7d37f378d6c426",
          "contractType": "4 - Days Rental",
        }
      "price": 10
    }
  ]
}

Thanks in advance.

答案1

得分: 1

一种选择是使用$lookup与管道,并在$map中使用$mergeObjects更新您的$set步骤:

db.products.aggregate([
  {$lookup: {
      from: "rentalcontractdetails",
      localField: "productPrice.rentalContractId",
      foreignField: "_id",
      as: "rentalContract",
      pipeline: [{$project: {contractType: 1}}]
  }},
  {$project: {
      _id: 0,
      productPrice: {$map: {
          input: "$rentalContract",
          in: {$mergeObjects: [
              {rentalContractId: "$$this"},
              {price: {$arrayElemAt: [
                    "$productPrice.price",
                    {$indexOfArray: ["$productPrice.rentalContractId", "$$this._id"]}
              ]}}
          ]}
      }}
  }}
])

了解它在playground示例中是如何工作的。

  • mongodb的$lookup和mongoose的populate是两种不同的东西,它们使用两种不同的机制。原始问题混合了这两种,但在代码中使用了$lookup,所以我猜这就是意图。
英文:

One option is to use $lookup with pipeline, and update your $set step to use $mergeObjects inside $map:

db.products.aggregate([
  {$lookup: {
      from: "rentalcontractdetails",
      localField: "productPrice.rentalContractId",
      foreignField: "_id",
      as: "rentalContract",
      pipeline: [{$project: {contractType: 1}}]
  }},
  {$project: {
      _id: 0,
      productPrice: {$map: {
          input: "$rentalContract",
          in: {$mergeObjects: [
              {rentalContractId: "$$this"},
              {price: {$arrayElemAt: [
                    "$productPrice.price",
                    {$indexOfArray: ["$productPrice.rentalContractId", "$$this._id"]}
              ]}}
          ]}
      }}
  }}
])

See how it works on the playground example

  • mongodb $lookup and mongoose populate are two different things, working with two different mechanisms. The original question was mixing the two, but used $lookup in the code, so I guess this was the intention.

huangapple
  • 本文由 发表于 2023年5月14日 04:22:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/76244721.html
匿名

发表评论

匿名网友

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

确定