mongodb 更改Mongo DB查询结果集中的元素名称

hc2pp10m  于 2023-01-20  发布在  Go
关注(0)|答案(2)|浏览(103)

我有如下命名为"FormData"的集合,

{
  "_id": ObjectId("5e3c27bf1ef77236945ef07b"),
  "eed12747-0923-4290-b09c-5a05107f5609": "20200206",
  "bd637691-782d-4cfd-8624-feeedfe11b3e": "20200206_1@mail.com"
}

我有另一个名为"Form"的集合,其中包含字段标题,

{
   "_id": ObjectId("5e3c27bf1ef77236945ef07b"),
   "Fields":[
     {
       "FieldID": "eed12747-0923-4290-b09c-5a05107f5609",
       "Title": "Phone"
     },
     {
       "FieldID": "bd637691-782d-4cfd-8624-feeedfe11b3e",
       "Title": "Email"
     }]
}

现在我必须Map元素名称与表单字段标题,我需要如下结果,

{
  "_id": ObjectId("5e3c27bf1ef77236945ef07b"),
  "Phone": "20200206",
  "Email": "20200206_1@mail.com"
}

请帮我解决这个问题。
先谢了!

d8tt03nd

d8tt03nd1#

您可以:

  1. $objectToArray,将$$ROOT文档转换为k-v对数组,以供将来查找
    1.使用$lookup中的子管道通过uuid查找值
    1.使用$mergeObject将原始值(即"20200206 ...")与查找到的新字段名称(即"Phone ...")组合在一起
    1.使用$arrayToObject$replaceRoot将结果转换回原始形式
db.FormData.aggregate([
  {
    $match: {
      "_id": ObjectId("5e3c27bf1ef77236945ef07b")
    }
  },
  {
    $project: {
      kv: {
        "$objectToArray": "$$ROOT"
      }
    }
  },
  {
    $unwind: "$kv"
  },
  {
    "$lookup": {
      "from": "Form",
      "let": {
        uuid: "$kv.k"
      },
      "pipeline": [
        {
          $match: {
            "_id": ObjectId("5e3c27bf1ef77236945ef07b")
          }
        },
        {
          "$unwind": "$Fields"
        },
        {
          $match: {
            $expr: {
              $eq: [
                "$$uuid",
                "$Fields.FieldID"
              ]
            }
          }
        },
        {
          $project: {
            _id: false,
            k: "$Fields.Title"
          }
        }
      ],
      "as": "formLookup"
    }
  },
  {
    $unwind: "$formLookup"
  },
  {
    $project: {
      kv: {
        "$mergeObjects": [
          "$kv",
          "$formLookup"
        ]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      kv: {
        $push: "$kv"
      }
    }
  },
  {
    "$project": {
      newDoc: {
        "$arrayToObject": "$kv"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          {
            "_id": "$_id"
          },
          "$newDoc"
        ]
      }
    }
  }
])

Mongo Playground

55ooxyrt

55ooxyrt2#

另一种选择是从Form集合开始并避开$unwind

  1. $match$lookup,将所有需要的数据放入一个文档
  2. $objectToArray以获取FormData的已知密钥
    1.使用$indexOfArray$arrayElemAt匹配项,并使用$mergeObjects合并它们。然后使用arrayToObject化响应
db.Form.aggregate([
  {$match: {_id: ObjectId("5e3c27bf1ef77236945ef07b")}},
  {$lookup: {
      from: "FormData",
      localField: "_id",
      foreignField: "_id",
      as: "formLookup",
      pipeline: [{$project: {_id: 0}}]
  }},
  {$set: {formLookup: {$objectToArray: {$first: "$formLookup"}}}},
  {$replaceRoot: {
      newRoot: {
        $mergeObjects: [
          {$arrayToObject: {
              $map: {
                input: "$formLookup",
                in: {$mergeObjects: [
                    {v: "$$this.v"},
                    {k: {$getField: {
                          input: {$arrayElemAt: [
                                "$Fields", 
                                {$indexOfArray: ["$Fields.FieldID", "$$this.k"]}
                          ]},
                          field: "Title"
                    }}}
                ]}
              }
          }},
          {_id: "$_id"}
        ]
      }
  }}
])

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

相关问题