MongoDB: Aggregate and flatten an array field

Question

After 28 years working with relational databases (SQL Server, MySQL, Oracle, Informix) I have moved to MongoDB. It has been slow going over the last two weeks. I would like to submit a couple of questions to confirm my thoughts.

My document looks like the following (ignore groupings 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"}
    ]
}

I would like to flatten the timepoints nested array. The following works, but is there a way to wildcard the attributes in timepoints instead of listing each one? The reason could be if a new attribute (e.g., 'c') is added to the subdocument I then have to modify the code or if this subdocument had a lot of attributes I would need to list each one instead of using a wildcard, if possible.

db.records.aggregate( {$unwind : "$timepoints"}, 
                      {$project: {_id: 1, site: 1, user: 1, 
                                  'timepoint': '$timepoints.timepoint', 
                                  'a': '$timepoints.a', 
                                  'b': '$timepoints.b'}})

Result:

{"id":"xyz-800", "site":"xyz", "user":800, "timepoint": 0, "a":1500, "b":700}
{"id":"xyz-800", "site":"xyz", "user":800, "timepoint": 2, "a":1000, "b":200}
{"id":"xyz-800", "site":"xyz", "user":800, "timepoint": 4, "a":3500, "b":1500}

I am currently using MongoDB 3.2

s7vr · Accepted Answer · 2016-11-28 01:01:37Z

11

Starting MongoDb 3.4, we can use $addFields to add the top level fields to the embedded document and use $replaceRoot to promote embedded document to top level.

db.records.aggregate({
    $unwind: "$timepoints"
}, {
    $addFields: {
        "timepoints._id": "$_id",
        "timepoints.site": "$site",
        "timepoints.user": "$user"
    }
}, {
    $replaceRoot: {
        newRoot: "$timepoints"
    }
})

Sample Output

{ "timepoint" : 0, "a" : 1500, "b" : 700, "_id" : "xyz-800", "site" : "xyz", "user" : 800 }
{ "timepoint" : 2, "a" : 1000, "b" : 200, "_id" : "xyz-800", "site" : "xyz", "user" : 800 }
{ "timepoint" : 4, "a" : 3500, "b" : 1500, "_id" : "xyz-800", "site" : "xyz", "user" : 800 }

edited Nov 28, 2016 at 1:01

answered Nov 28, 2016 at 0:43

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

1 Comment

C. Wolcott Over a year ago

Thank you very much, I was hoping for something like 'timepoints.*': 1. Interesting new operators in 3.4.

Collectives™ on Stack Overflow

MongoDB: Aggregate and flatten an array field

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related