MongoDB - Query on the last object of an array?

Question

Is there any way in MongoDB in which I can specifically query on last object's key value in an array in a single db query.

For eg: This is my doc in a collection.

{
  "com" : [ 
    { "ts" : 1510830164203, "com" : "com1" }, 
    { "ts" : 1511242569673, "com" : "connected" },
    { "ts" : 1511244832741, "com" : "vb" } 
  ],
  "status" : [ 
    { "ts" : 1510857000000, "stat" : 3 } 
  ] 
}

So as you can see there are multiple objects in com. How can I query on last object's ts(timestamp) or I want to check is last com inserted in between today's date or not.

I have already gone through this link. But didn't find the appropriate solution.

Any help can be appreciated.

Shaishab Roy · Accepted Answer · 2017-11-21T07:22:03.463

2

You can use $arrayElemAt to get the last element and then match applied in aggregation. To get the last element using $arrayElemAt use second value -1 that indicate last element of an array. $arrayElemAt: ["arrayName", -1]. code will be like

db.collectionName.aggregate([
  {
    $project: {
      status: 1,
      com: {$arrayElemAt: ["$com", -1]}
    }
  },
  {
    $match: {"com.ts": 15115465465}
  }
])

N.B: if you want to compare like less than or greater than then use like : $lt, $lte, $gt, or $gte any one that you need

$match: {"com.ts": {$lt: 15115465465}}

edited Nov 21 '17 at 07:22

answered Nov 21 '17 at 07:16

Shaishab Roy

16,335
7
50
68

Yes, i think this can do the trick. But just tell me one thing. How heavy is this query? I mean I have to use this sort of query more frequently. I hope this won't cause any problem right? – Saif Ali Khan Nov 21 '17 at 07:32
Yes that's not heavy query, simple query – Shaishab Roy Nov 21 '17 at 07:36

Elie Asmar · Answer 2 · 2017-11-21T07:27:14.693

0

var d1 = new Date( parseInt( "Today at 12:00 AM", 16 ) * 1000 )
var d2 = new Date( parseInt( "Tomorrow at 12:00 AM", 16 ) * 1000 )

db.table.find(
   { com: { $elemMatch: {ts:{ $gte: d1, $lt: d2 } } } })
)

edited Nov 21 '17 at 07:27

answered Nov 21 '17 at 07:17

Elie Asmar

2,995
4
17
30

Can we use it for the last object's key ? I mean it will search in the whole array. – Saif Ali Khan Nov 21 '17 at 07:37
No, I only need that object that satisfy condition on last object's key – Saif Ali Khan Nov 21 '17 at 07:49
The result will return all objects that satisfy the condition of Today's date. Now you can make a max() aggregate to take the maximum ts (supposing the maximum has the highest value). Or you can apply the following:db.table.find( { com: { $elemMatch: {ts:{ $gte: d1, $lt: d2 } } } }) )sort({ts:-1}).limit(1) – Elie Asmar Nov 21 '17 at 07:52
Don't you think the above aggregate query will be more easier than this? – Saif Ali Khan Nov 21 '17 at 07:54
There is no easier, both are solutions to your question. – Elie Asmar Nov 21 '17 at 08:19
Ok, I think using aggregation will be more flexible. – Saif Ali Khan Nov 21 '17 at 08:30

score 0 · Answer 3 · answered Nov 25 '17 at 14:22

db.collection.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $match: {
             "_id" : ObjectId("5a197a3bde472b16ed9fc28d")
            }
        },

        // Stage 2
        {
            $unwind: {
                path : "$com"
            }
        },

        // Stage 3
        {
            $sort: {
             'com.ts':-1
            }
        },

        // Stage 4
        {
            $limit: 1
        }

    ]



);

score 0 · Answer 4 · edited Jan 08 '19 at 12:50

0

You can use the below project query if you only need to find the last element.

db.collection.find({},{status: 1, com:{$slice: -1}})

More discussion on the similar topic here

edited Jan 08 '19 at 12:50

Moshe Slavin

5,127
5
23
38

answered Jan 08 '19 at 10:25

PJ-ZO

1
2

More discussion on similar topic [here](https://stackoverflow.com/questions/28680295/mongodb-query-on-the-last-element-of-an-array) – PJ-ZO Jan 08 '19 at 10:31
where is the discussion? – Moshe Slavin Jan 08 '19 at 10:32
Please follow the hyperlink or use the URL https://stackoverflow.com/questions/28680295/mongodb-query-on-the-last-element-of-an-array – PJ-ZO Jan 08 '19 at 10:51

MongoDB - Query on the last object of an array?

4 Answers4