Query on the last element of an array?

[jpbla...@gmail.com]

I know that MongoDB supports the syntax find{array.0.field:"value"}, but I specifically want to do this for the last element in the array, which means that I don't know the index.  Essentially, I want find() to only return documents where the last element in an array matches a certain value.  Is there some kind of operator for this, or am I out of luck?  Thanks.

[Will Berkeley]

Without a known length for the array, you cannot reference the last element of the array using the "array.i" syntax. There are other ways of maintaining the array that might allow you to accomplish what you want to do anyway:

1. Use $push with $each and $position to insert elements to the front of the array, then reference array.0:

> db.test.drop()

> db.test.insert({ "_id" : 0, "x" : [1, 2, 3] })

> db.test.update({ "_id" : 0 }, { "$push" : { "x" : { "$each" : [4], "$position" : 0 } } })

> db.test.findOne()

{ "_id" : 0, "x" : [ 4, 1, 2, 3 ] }

> db.test.count({ "x.0" : 4 })

1

$position is new in 2.6.

2. Use $push with $each and $sort to maintain the array in a sorted order to that your current last element appears first:

> db.test.drop()

> db.test.insert({ "_id" : 0, "x" : [1, 2, 3] })

> db.test.update({ "_id" : 0 }, { "$push" : { "x" : { "$each" : [0], "$sort" : 1 } } })

> db.test.findOne()

{ "_id" : 0, "x" : [ 4, 1, 2, 3 ] }

> db.test.count({ "x.0" : 0 })

1

This works only if some `$sort` works for your use case, naturally.

3. Maintain the last value pushed onto the array in a separate field:

> db.test.drop()

> db.test.insert({ "_id" : 0, "x" : [1, 2, 3], "last" : 3 })

> db.test.update({ "_id" : 0 }, { "$push" : { "x" : 4 }, "$set" : { "last" : 4 } })

> db.test.findOne()

{ "_id" : 0, "x" : [ 1, 2, 3, 4 ] }

> db.test.count({ "last" : 4 })

1

-Will

[Will Berkeley]

Slight correction to the last example (3):

> db.test.drop()

> db.test.insert({ "_id" : 0, "x" : [1, 2, 3], "last" : 3 })

> db.test.update({ "_id" : 0 }, { "$push" : { "x" : 4 }, "$set" : { "last" : 4 } })

> db.test.findOne()

{ "_id" : 0, "x" : [ 1, 2, 3, 4 ], "last" : 4 }

> db.test.count({ "last" : 4 })

1

[jpbla...@gmail.com]

Will,

Thanks for your response.  I had a hunch that it wasn't possible, but wanted to make sure.  I'll probably end up doing something along the lines of Option 3, since Option 1 would involve using a pre-release version of the Spring driver.

[Wxll]

Hi,

Since mongodb 3.0 is out, is it still not possible to use "find() to only return documents where the last element in an array matches a certain value" like the question below from early 2015 with mongo 2.6

Many thanks for the update of this topic...

Wxll

[Asya Kamsky]

No, still not possible in 3.0 but you will be able to do it with aggregate in upcoming 3.2. 

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/b937cc5b-efd0-4e26-a995-ab4f94de0969%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
Free MongoDB Monitoring - cloud.mongodb.com
Free Online Education - university.mongodb.com
Get Involved - mongodb.org/community
We're Hiring! - https://www.mongodb.com/careers

[Alex Filgs]

Hi,
I have exactly the same problem and I really do not like any of the workarounds suggested and also at stackOverflow https://stackoverflow.com/questions/28680295/mongodb-query-on-the-last-element-of-an-array

The simplest form would be to change the MongoDB syntax like this proposal (direct to the point with extreme flexibility):

Single item:
array.0.field  => first item
array.n.field => item at position n
array.-1.field => last item
array.-2.field => one item before the last

Range of items:
array.m..n.field => start item m, finish item n
array.0..4.field => first 5 items
array.-5..-1.field => last 5 items
array.0..-1.field => all items
array.0..-2.field => all items except the last one
array.1..-1.field => all items except the first one

Note: This suggestion is a combination of Lua and Pascal syntaxes and I use it in a private Lua Library with great flexibility.

@Asya Kamsky, please consider this syntax evolution on next MongoDB versions

Thanks
Filgs.