CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
C. Wolcott
C. Wolcott

Reputation: 189

Aggregate array of subdocuments into single document

My document looks like the following (ignore timepoints for this question):

{
    "_id": "xyz-800",
    "site": "xyz",
    "user": 800,
    "timepoints": [
        {"timepoint": 0, "a": 1500, "b": 700},
        {"timepoint": 2, "a": 1000, "b": 200},
        {"timepoint": 4, "a": 3500, "b": 1500}
    ],
    "groupings": [
        {"type": "MNO", "group": "<10%", "raw": "1"},
        {"type": "IJK", "group": "Moderate", "raw": "23"}
    ]
}

Can I flatten (maybe not the right term) so the groupings are in a single document. I would like the result to look like:

{
    "id": "xyz-800", 
    "site": "xyz", 
    "user": 800, 
    "mnoGroup": "<10%", 
    "mnoRaw": "1", 
    "ijkGroup": "Moderate", 
    "ijkRaw": "23"
}

In reality I would like the mnoGroup and mnoRaw attributes to be created no matter if the attribute groupings.type = "MNO" exists or not. Same with the ijk attributes.

Upvotes: 0

Views: 421

Answers (1)

s7vr
s7vr

Reputation: 75914

You can use $arrayElemAt to read the groupings array by index in the first project stage and $ifNull to project optional values in the final project stage. Litte verbose, but'll see what I can do.

db.groupmore.aggregate({
    "$project": {
        _id: 1,
        site: 1,
        user: 1,
        mnoGroup: {
            $arrayElemAt: ["$groupings", 0]
        },
        ijkGroup: {
            $arrayElemAt: ["$groupings", -1]
        }
    }
}, {
    "$project": {
        _id: 1,
        site: 1,
        user: 1,
        mnoGroup: {
            $ifNull: ["$mnoGroup.group", "Unspecified"]
        },
        mnoRaw: {
            $ifNull: ["$mnoGroup.raw", "Unspecified"]
        },
        ijkGroup: {
            $ifNull: ["$ijkGroup.group", "Unspecified"]
        },
        ijkRaw: {
            $ifNull: ["$ijkGroup.raw", "Unspecified"]
        }
    }
})

Sample Output

{ "_id" : "xyz-800", "site" : "xyz", "user" : 800, "mnoGroup" : "<10%", "mnoRaw" : "1", "ijkGroup" : "Moderate", "ijkRaw" : "23" }
{ "_id" : "ert-600", "site" : "ert", "user" : 8600, "mnoGroup" : "Unspecified", "mnoRaw" : "Unspecified", "ijkGroup" : "Unspecified", "ijkRaw" : "Unspecified" }

Upvotes: 1

Related Questions