Create new properties based on embedded array elements in MongoDB

Question

I have this kind of document

{ 
    "_id" : ObjectId("573342930348ce88ff1685f3"),
    "presences" : [
        {
            "_id" : ObjectId("573342930348ce88ff1685f2"), 
            "createdAt" : NumberLong(1458751869000) 
        }, 
        {
            "_id" : ObjectId("573342930348ce88ff1685f5"), 
            "createdAt" : NumberLong(1458751885000)
        }, 
        {
            "_id" : ObjectId("573342930348ce88ff1685f7"), 
            "createdAt" : NumberLong(1458751894000)
        }
    ]
}

How can I extract first and last presences element to new properties firstPresence and lastPresence like this?

{ 
    "_id" : ObjectId("573342930348ce88ff1685f3"),
    "firstPresence": {
        "_id" : ObjectId("573342930348ce88ff1685f2"), 
        "createdAt" : NumberLong(1458751869000) 
    },
    "lastPresence": {
        "_id" : ObjectId("573342930348ce88ff1685f7"), 
        "createdAt" : NumberLong(1458751894000)
    },
    "presences" : [
        ...
    ]
}

I want to use a query that can be applied to all documents in one time.

mickl · Accepted Answer · 2017-12-28 11:11:18Z

2

You need to $unwind your presences array to do the aggregation. Before grouping you can sort them by createdAt to utilize $first and $last operators.

db.collection.aggregate(
    [
        { $unwind: "$presences" },
        { $sort: { "presences.createdAt": 1 } },
        { 
            $group: {
                _id: "$_id",
                "presences": { $push: "$presences" },
                "lastPresence": { $last: "$presences" },
                "firstPresence": { $first: "$presences" },
            } 
        },
        { $out : "collection" }
    ])

Last aggregation pipeline ($out) will replace existing collection.

edited Dec 28, 2017 at 11:11

answered Dec 28, 2017 at 10:36

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

2 Comments

Franck Anso Over a year ago

It will replace my documents or create new ones?

mickl Over a year ago

Sorry, I thought that you want to project your data. If you need to replace existing collection you should add $out as a last step - just as I edited my answer.

Rubin Porwal · Accepted Answer · 2017-12-28 10:59:31Z

0

According to above mentioned description as a solution to it please try executing following aggregate query into MongoDB shell

   db.collection.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $project: {

                first: {
                    $arrayElemAt: ["$presences", 0]
                },
                last: {
                    $arrayElemAt: ["$presences", -1]
                },
                presences: 1
            }
        },

    ]



);

answered Dec 28, 2017 at 10:59

Rubin Porwal

3,8451 gold badge27 silver badges27 bronze badges

Collectives™ on Stack Overflow

Create new properties based on embedded array elements in MongoDB

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related