skip to Main Content

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!

2

Answers


  1. 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

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search