Mongodb find comparing array elements

Question

I have a collection with about 200K documents like this:

db.place.find()[0]
{
    "_id" : ObjectId("5290de1111afb260363aa4a1"),
    "name" : "place X",
    "center" : [x, y]   
}

Now I`m trying to query for the places where the center Y is greater than X, and having the following problem:

> db.place.find({'center.0':{'$gt':center.1}}).count()
Sat Nov 23 14:42:01.556 JavaScript execution failed: SyntaxError: Unexpected number

Any hints? Thanks in advance

should be: `db.place.find({'center.0':{'$gt':'center.1'}}).count()` Notice the quotes around center.1 — idbentley, Nov 23 '13 at 17:10
I've tried this query, but it seems that it doesn't work. I am using version 2.4.7. I've poted answer with $where operator instead. — Victoria Malaya, Nov 23 '13 at 20:32
you cannot refer to the contents of the document on the right hand side of where (i.e {a:1} is okay, {a:b} where b is another field is not okay. If you need to compare x and y you might consider that the schema isn't best for your use case - do you have to store x and y like this? — Asya Kamsky, Nov 23 '13 at 22:50
there may be a way to do this without using $where. is "center" always an array with two elements or might it have more or fewer elements? And what is the type of "x" and "y" - are they numbers? — Asya Kamsky, Nov 23 '13 at 22:56
Hey all, thanks for the replies... In fact the solution with {'$gt':'center.1'} is returning 0, since it compares float and string.. — André Teixeira, Nov 26 '13 at 18:01
I could make it work with $where, but since it evaluaes JS and do not take advange of indexes, I`m looking for an alternative solution... @AsyaKamsky, center is a Lat Long pair.. They are float and their size is fixed — André Teixeira, Nov 26 '13 at 18:04
then you can do it with aggregation framework - I'll post as an answer. — Asya Kamsky, Nov 27 '13 at 02:21
hold on - are you sure you mean lat, long? In MongoDB coordinates are supposed to be stored as long, lat pairs. <- x, y - so do you want x to be greater than y? — Asya Kamsky, Nov 27 '13 at 02:40
Wow... Just checked the docs for that fact of lon lat instead of lat lon... In Brazil we use the second form, and all the database is that way, the problem started when we loaded a dataset with markers in the correct way (that I thought was whe wrong format).. Anyway will try you aggregation solution to fix it all, it is a solution for me since I'm sure all my lons are lower than lats. — André Teixeira, Nov 27 '13 at 13:34

score 4 · Answer 1 · answered Nov 23 '13 at 19:56

It seems that you need to use $where operator instead.

db.place.find({$where: function() {return this.center[0] > this.center[1]}})

For example, there are 3 documents in collection:

{ "_id" : ObjectId("52910457c7d99f10949e5a85"), "name" : "place X", "center" : [ 2,  3 ] }
{ "_id" : ObjectId("52910463c7d99f10949e5a86"), "name" : "place Y", "center" : [ 3,  2 ] }
{ "_id" : ObjectId("5291046ac7d99f10949e5a87"), "name" : "place Y", "center" : [ 8,  9 ] }

The result of the $where command will be:

{ "_id" : ObjectId("52910463c7d99f10949e5a86"), "name" : "place Y", "center" : [ 3,  2 ] }

Hello Victoria, thank you for the response.. As Salvador Dali said, it is not a performatic solution... Will keep looking for a faster way. — André Teixeira, Nov 26 '13 at 18:07

score 4 · Accepted Answer · answered Nov 27 '13 at 02:52

Because you happen to have exact format of the field every time (circle is a two element array) you can transform it in aggregation framework into two fields and then compare them in a projection, and match to get back just the elements satisfying your requirement of second array element being greater than first array element.

db.place.aggregate( [
      { $unwind : "$center" },
      { $group : { _id : "$_id", 
                   centerX : {$first:"$center"}, 
                   centerY : {$last:"$center"} 
      } },
      { $project : { YgtX : { $gt : [ "$centerY", "$centerX" ] } } },
      { $match : { YgtX : true } }
] );

Now, if your array was an arbitrary pair of numerical values, then you can use the above.

You said in comments that your pair represented coordinates (lat, long) - keep in mind that in MongoDB coordinate pairs are always stored as long, lat - if your actual x, y values were coordinates in on a flat (as opposed to spherical) place, you could find all the documents that had Y coordinate greater than X coordinate with a single geospatial query:

db.place.find( { center : { $geoWithin : { $geometry : {
                  type:"Polygon", 
                  coordinates:[[[50,50],[-50,50],[-50,-50],[50,50]]]
} } } } );

The above query assumes that your coordinate system goes from -50 to 50 along X and Y and it finds all points in the triangle that represents all coordinates having Y >= X.

score 2 · Answer 3 · edited May 23 '17 at 12:06

You can not do the query you want in a simple way in mongo because mongo does not support searching or updating based on the element in the collection. So even such simple document as {a : 1, b : 1} and find the document where a = b is impossible without $where clause.

The solution suggested by idbentley db.place.find({'center.0':{'$gt':'center.1'}}) will not work as well (also will not generate an error) because this way you will compare center.0 to a string 'center.1'. Therefore correct solution is the solution of Victoria Malaya (but she forgot to put .count() in the end).

One thing I would like to suggest. Anything with where is very very slow. So if you plant to do this query more then once, think about creating additional field which will store this precomputed result (you can do it in a similar fashion with this answer).

score 0 · Answer 4 · answered Mar 16 '21 at 19:23

You can also use the $expr operator: https://docs.mongodb.com/manual/reference/operator/query/expr/

db.place.find({$expr: {$gt: ['$center.0', '$center.1']}}).count()

However: Similar to $where $expr is very slow, read here for details: Mongodb $expr query is very slow

Example here: https://medium.com/@deepakjoseph08/mongodb-query-expressions-that-compare-fields-from-the-same-document-ccb45a1f064b

Mongodb find comparing array elements

4 Answers4

Linked

Related