MongoDB - Pivot data down instead of flattening with aggregation query

user_16132704 0
Viewed 56

Can we unwind/flatten both old and new arrays and pivote data down like the below examples, consider both arrays might have different sizes and order. Looking for a solution in mongo aggregation query

Ex.1: JSON/Object

{
    "sno": "1001",
    "owner": "Tim",
    "old": [
        {
            "name": "John",
            "age": "20"
        },
        {
            "name": "Park",
            "age": "40"
        }
    ],
    "new": [
        {
            "name": "Snow",
            "age": "10"
        },
        {
            "name": "Mike",
            "age": "25"
        },
        {
            "name": "New Rec",
            "age": "55"
        }
    ]
}

Output

sno     owner   current_name    current_age     renew_name  renew_age
----------------------------------------------------------------------
1001    Tim     John            20              Snow        10
1001    Tim     Park            40              Mike        25
1001    Tim                                     New Rec     55

Ex.2: JSON/Object

{
    "sno": "1001",
    "owner": "Tim",
    "old": [
        {
            "name": "John",
            "age": "20"
        },
        {
            "name": "Park",
            "age": "40"
        }
    ],
    "new": [
        {
            "name": "Snow",
            "age": "10"
        }
    ]
}

Output

sno     owner   current_name    current_age     renew_name  renew_age
----------------------------------------------------------------------
1001    Tim     John            20              Snow        10
1001    Tim     Park            40
aggregation-framework mongodb-query mongodb
1 Answers
  • $project to show required fields
  • $range to make array from 0 to total max element size of old or new array
  • $map to iterate loop of the above range
  • $arrayElemAt to select the object of the specific element from old and new array
  • $unwind deconstruct names array
  • $project to format your result and show required fields
db.collection.aggregate([
  {
    $project: {
      sno: 1,
      owner: 1,
      names: {
        $map: {
          input: {
            $range: [0,
              {
                $cond: [
                  { $gt: [{ $size: "$old" }, { $size: "$new" }] },
                  { $size: "$old" },
                  { $size: "$new" }
                ]
              }
            ]
          },
          in: {
            old: { $arrayElemAt: ["$old", "$$this"] },
            new: { $arrayElemAt: [ "$new", "$$this"] }
          }
        }
      }
    }
  },
  { $unwind: "$names" },
  {
    $project: {
      sno: 1,
      owner: 1,
      current_name: "$names.old.name",
      current_age: "$names.old.age",
      renew_name: "$names.new.name",
      renew_age: "$names.new.age"
    }
  }
])

Playground

user_89871280
Related