Flatten MongoDB nested array in aggregation

Question

I have documents like this:

{
    "many" : {},
    "other" : {},
    "fields" : {},
    "phases" : [ 
        {
            "type" : 10,
            "states" : [ 
                {
                    "type" : 10,
                    "time" : ISODate("2018-04-25T13:06:42.990+02:00")
                }, 
                {
                    "type" : 20,
                    "time" : ISODate("2018-04-25T13:26:12.122+02:00")
                }, 
                {
                    "type" : 30,
                    "time" : ISODate("2018-04-25T13:26:30.124+02:00")
                }
            ]
        }, 
        {
            "type" : 20,
            "states" : [ 
                {
                    "type" : 10,
                    "time" : ISODate("2018-04-25T13:27:58.201+02:00")
                }
            ]
        }
    ]
}

Within an aggregation, I'm trying to flatten the states including the parent's type like this (desired output):

"states" : [ 
    {
        "phase": 10,
        "type" : 10,
        "time" : ISODate("2018-04-25T13:06:42.990+02:00")
    }, 
    {
        "phase": 10,
        "type" : 20,
        "time" : ISODate("2018-04-25T13:26:12.122+02:00")
    }, 
    {
        "phase": 10,
        "type" : 30,
        "time" : ISODate("2018-04-25T13:26:30.124+02:00")
    }, 
    {
        "phase": 20,
        "type" : 10,
        "time" : ISODate("2018-04-25T13:27:58.201+02:00")
    }
]

I already accomplished the additional states field without the phase field with this aggregation:

db.docs.aggregate([
{
    $addFields: {
        states: {
            $reduce: {
                  input: "$phases",
                  initialValue: [],
                  in: { $concatArrays: ["$$value", "$$this.states"] }
               }
           }
    }
}
])

The 'many other fields' should be preserved, so I believe grouping is not an option.
MongoDB version is 3.4.

I tried many things without result. I wonder if and how this is possible.

Mohit Kumar Bordia · Accepted Answer · 2018-09-04 12:03:41Z

8

db.state.aggregate(

// Pipeline
[
    // Stage 1
    {
        $unwind: {
            path : "$phases",
            includeArrayIndex : "arrayIndex", // optional
            preserveNullAndEmptyArrays : false // optional
        }
    },

    // Stage 2
    {
        $unwind: {
            path : "$phases.states",
            includeArrayIndex : "arrayIndex", // optional
            preserveNullAndEmptyArrays : false // optional
        }
    },

    // Stage 3
    {
        $project: {
            "phases":"$phases.type",
            "type":"$phases.states.type",
            "time":"$phases.states.time",
        }
    },

    // Stage 4
    {
        $group: {
            "_id":"$_id",
            states: { $push:  { phases: "$phases", type: "$type",time: "$time" } }
        }
    },
]);

answered Sep 4, 2018 at 12:03

Mohit Kumar Bordia

1687 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

s7vr · Accepted Answer · 2018-09-04 13:13:38Z

4

You can use below aggregation. Use $map to format the states array to include phase field.

db.docs.aggregate([
  {"$addFields":{
    "states":{
      "$reduce":{
        "input":"$phases",
        "initialValue":[],
        "in":{
          "$concatArrays":[
            "$$value",
            {"$map":{
              "input":"$$this.states",
              "as":"state",
              "in":{"phase":"$$this.type","type":"$$state.type","time":"$$state.time"}
            }}
          ]
        }
      }
    }
  }}
])

answered Sep 4, 2018 at 13:13

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Comments

Juan Bermudez · Accepted Answer · 2018-09-04 12:33:30Z

The code of Mohit Kumar Bordia looks great. In addition, if you want to also return the old object (based in Mohit code):

db.getCollection('flatten').aggregate(

// Pipeline
[
    // Stage 1
    {
        $unwind: {
            path : "$phases",
            includeArrayIndex : "arrayIndex", // optional
            preserveNullAndEmptyArrays : false // optional
        }
    },

    // Stage 2
    {
        $unwind: {
            path : "$phases.states",
            includeArrayIndex : "arrayIndex", // optional
            preserveNullAndEmptyArrays : false // optional
        }
    },

    // Stage 3
    {
        $project: {
            "_id" : "$_id",
            "many" : "$many",
            "other" :"$other",
            "fields" : "$fields",
            "phases":"$phases.type",
            "type":"$phases.states.type",
            "time":"$phases.states.time",
        }
    },

    // Stage 4
    {
        $group: {
            "_id":"$_id",
            many : { $first: "$many"},
            other: { "$first": "$other" },
            fields: { "$first": "$fields" },
            states: { $push:  { phases: "$phases", type: "$type",time: "$time" } }
        }
    },
]);

Ashh · Accepted Answer · 2018-09-04 18:38:56Z

1

You need to loop over each array using $map and then you can use $reduce to $concat the final arrays

db.collection.aggregate([
  { "$addFields": {
    "states": { "$reduce": {
      "input": { "$map": {
        "input": "$phases",
        "as": "pa",
        "in": { "$map": {
          "input": "$$pa.states",
          "as": "st",
          "in": { "type": "$$st.type", "time": "$$st.time", "phase": "$$pa.type" }
        }}
      }},
      "initialValue": [],
      "in": { "$concatArrays": ["$$value", "$$this"] }
    }}
  }}
])

edited Sep 4, 2018 at 18:38

answered Sep 4, 2018 at 12:27

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Collectives™ on Stack Overflow

Flatten MongoDB nested array in aggregation

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related