I have collection like below named as "FormData",

{
  "_id": ObjectId("5e3c27bf1ef77236945ef07b"),
  "eed12747-0923-4290-b09c-5a05107f5609": "20200206",
  "bd637691-782d-4cfd-8624-feeedfe11b3e": "[email protected]"
}

I have another collection named as "Form" which will have Title of Fields,

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

Now I have to map element name with Form field title and I need result like below,

{
  "_id": ObjectId("5e3c27bf1ef77236945ef07b"),
  "Phone": "20200206",
  "Email": "[email protected]"
}

Please help me to solve this.

Thanks in advance!

CodePudding user response：

You can:

1. $objectToArray to convert the $$ROOT document into an array of k-v pairs for future lookups
2. use a sub-pipeline in $lookup to find the value by the uuid
3. use $mergeObject to combine the original values(i.e. "20200206"...) with the new field name looked up (i.e. "Phone"...)
4. wrangle the result back into original form using $arrayToObject and $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

CodePudding user response：

Another option is to start from Form collection and avoid $unwind:

1. $match and $lookup to get all needed data into one document
2. $objectToArray to get known keys for FormData
3. Match the items using $indexOfArray and $arrayElemAt and merge them using $mergeObjects. Then use arrayToObject to format the response

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"}
        ]
      }
  }}
])

See how it works on the playground example