Return last document per unique subfield with subfields

Question

I'm trying to get the last documents per user in my collection, but I'm not able to retrieve that successfully. I already looked in other posts, but couldn't find (mongo group query how to keep fields and MongoDB : Aggregation framework : Get last dated document per grouping ID , for example)

My problem is that I'm not able to return fields as objects apparently (I'm new to mongo so I hope there is a way).

All I can return are fields like {title, body, username, avatar} and not {title, body, user { username, avatar } }

My collection:

[{
    title: 'title 1',
    body: 'body 2',
    user: {
        username: 'a',
        avatar: 'avatar 1' 
    }
 },
 {
    title: 'title 2',
    body: 'body 2',
    user: {
        username: 'b',
        avatar: 'avatar 2' 
    }
 },
 {
    title: 'title 3',
    body: 'body 3',
    user: {
        username: 'b',
        avatar: 'avatar 2' 
    }
 }]

I want to return after group by:

[{
    title: 'title 1',
    body: 'body 2',
    user: {
        username: 'a',
        avatar: 'avatar 1' 
    }
 },
 {
    title: 'title 3',
    body: 'body 3',
    user: {
        username: 'b',
        avatar: 'avatar 2' 
    }
 }]

Example of one of the queries I tried:

// this only returns the avatar and not the username...
db.collection.aggregate([
{ '$sort': { title: 1 } },
{ '$group': { 
    _id: '$user.username',
    body: { $last: '$body' },
    title: { $last: '$title' },
    user: { $last: '$user.username', $last: '$user.avatar' } 
}}])
// this throws an error saying the field 'user ' must be an accumulator object
db.collection.aggregate([
    { '$sort': { title: 1 } },
    { '$group': {
         _id: '$user.username',
         body: { $last: '$body' },
         user: [ { $last: '$user.username', $last: '$user.avatar' } ] 
    }}
 ])

With that said, I also want to point out that maybe group is not the way to go. Maybe mongo has a distinct function that I could use?

When you say that you want to get the "last" document per user in your collection, do you mean the last by time? Last by index? What is your definition of "last"? — Keith Dawson, Apr 19 '19 at 02:35
Sorry, the definition would be the last document by time. I think the ObjectID that mongo creates has the timestamp encoded. So that could work too. The most important part is the return field as an object. I want a field containing another dictionary, and not just a string — renno, Apr 19 '19 at 12:45

Ashok · Accepted Answer · 2019-04-19T13:20:16.003

2

Add a createdAt field in the database as a timestamp if you want to filter data based on the date

db.collection.aggregate([
    { "$sort": { "createdAt": 1 } },
    {
      "$group": {
         _id: "$user.username",
         body: { $last: "$body" },
         title: { $last: "$title" },
         user: {
             $first: {
               username: "$user.username",
               avater: "$user.avatar"
             }
         },
      }
    }
])

When you search it always take data from last of collection if you want to take it first you need to read $natural

Please check here

edited Apr 19 '19 at 13:20

answered Apr 19 '19 at 05:18

Ashok

2,846
1
12
20

My problem is not filtering/sorting. I want to return a field containing objects and not a field containing a string like you have there. For example: user: { username: { $first: "$user.username"}, avatar: "$user.avatar" } } – renno Apr 19 '19 at 12:47
I have updated my answer as per your requirement. Please check and let me know if something wrong. – Ashok Apr 19 '19 at 13:02
This returns an empty array. Also why have the $project function there? – renno Apr 19 '19 at 13:14
Actually, it worked... I typed something wrong. But I'm still wondering about the $project. Thanks! – renno Apr 19 '19 at 13:15
1

Project is just to remove _id filed no need to that stage it is just selecting stage – Ashok Apr 19 '19 at 13:17

Return last document per unique subfield with subfields

1 Answers1