MongoDB using $and with slice and a match

Question

I'm using @Query from the spring data package and I want to query on the last element of an array in a document.

For example the data structure could be like this:

{
   name : 'John',
   scores: [10, 12, 14, 16]
},
{
   name : 'Mary',
   scores: [78, 20, 14]
},

So I've built a query, however it is complaining that "error message 'unknown operator: $slice' on server"

The $slice part of the query, when run separately, is fine:

db.getCollection('users').find({}, {scores: { $slice: -1 })

However as soon as I combine it with a more complex check, it gives the error as mentioned.

db.getCollection('users').find{{"$and":[{ } , {"scores" : { "$slice" : -1}} ,{"scores": "16"}]})

This query would return the list of users who had a last score of 16, in my example John would be returned but not Mary.

I've put it into a standard mongo query (to debug things), however ideally I need it to go into a spring-data @query construct - they should be fairly similar.

Is there anyway of doing this, without resorting to hand-cranked java calls? I don't see much documentation for @Query, other than it takes standard queries.

As commented with the link post, that refers to aggregate, how does that work with @Query, plus one of the main answers uses $where, this inefficient.

Possible duplicate of [MongoDB - Query on the last element of an array?](https://stackoverflow.com/questions/28680295/mongodb-query-on-the-last-element-of-an-array) — s7vr, Jan 23 '18 at 19:23
@Veeram I don't see a suitable answer there, they are either using aggregate - how does that work in an @ Query directive - I see nothing in the docs, also one of the main answers uses $where which again is not suitable as it's very slow. There could be 1000s of entries. — PeterS, Jan 23 '18 at 20:37
There is no @Query answer. I think the best answer is to store the documents in the reverse order where you can access the top document with .0 index. Something like `db.getCollection('users').find({"scores.0": 16})`. If that is not an option, you've to use MongoTemplate to access aggregate methods. — s7vr, Jan 23 '18 at 20:38
Come to think of it you can now use the `$expr` operator in 3.6 to use aggregation function in find query. So something like `{$expr:{$gt:[{$arrayElemAt:["scores", -1]}, 16]}}` in @Query. You can try `db.getCollection('users').find({$expr:{$gt:[{$arrayElemAt:["‌‌scores", -1]}, 16]}})` in 3.6 shell. I've updated the linked post to include the new answer. Tested in Spring Query directive & it works!. Try `@Query("{$expr:{$gt:[{$arrayElemAt:[\"scores\", -1]}, ?0]}}")` — s7vr, Jan 23 '18 at 21:35
@Veeram Thanks I will see If I can get this working in my environment. — PeterS, Jan 24 '18 at 10:04
@Veeram, sorry that's not worked in our environment, we are using on-line Mongo (cloud), so are restricted to versions I guess, the error message I get is: "errmsg" : "unknown top level operator: $expr", If you have any further suggestions then please let know. Yeah, just checked we are fixed on 3.4.10, is there a suitable solution for that version - or is it back to plain old java calls? — PeterS, Jan 24 '18 at 22:44
You can use MongoTemplate to execute aggregation. So essentially running the same query in the aggregation pipeline. Let me know if you are interested. — s7vr, Jan 24 '18 at 22:58
@Veeram should be OK with that, shame we have to break out of the "@Query" way of doing things, nevermind. — PeterS, Jan 25 '18 at 09:01

score 0 · Accepted Answer · answered Feb 27 '18 at 12:42

The general way forward with the problem is unfortunately the data, although @Veeram's response is correct, it will mean that you do not hit indexes. This is an issue where you've got very large data sets of course and you will see ever decreasing return times. It's something $where, $arrayElemAt cannot help you with. They have to pre-process the data and that means a full collection scan. We analysed several queries with these constructs and they involved a "COLSCAN".

The solution is ideally to create a field that contains the last item, for instance:

{
   name : 'John',
   scores: [10, 12, 14, 16],
   lastScore: 16
},
{
   name : 'Mary',
   scores: [78, 20, 14],
   lastScore: 14
}

You could create a listener to maintain this as follows:

@Component
public class ScoreListener extends AbstractMongoEventListener<Scores>

You then get the ability to sniff the data and make any updates:

@Override
public void onBeforeConvert(BeforeConvertEvent<Scores> event) {
      // process any score and set lastScore
}

Don't forget to update your indexes (!):

@CompoundIndex(name = "lastScore", def = "{"
        + "'lastScore': 1"
        + " }")

Although this does contain a disadvantage of a slight duplication of data, in current Mongo (3.4) this really is the only way of doing this AND to include indexes in the search mechanism. The speed differences were dramatic, from nearly a minute response time down to milliseconds.

In Mongo 3.6 there may be better ways for doing that, however we are fixed on this version, so this has to be our solution.

MongoDB using $and with slice and a match

1 Answers1