[SERVER-2702] Compare document fields in queries Created: 08/Mar/11  Updated: 05/Dec/17  Resolved: 05/Oct/17

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Minor - P4
Reporter: Remon van Vliet Assignee: Tess Avitabile (Inactive)
Resolution: Duplicate Votes: 35
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
duplicates SERVER-30731 Add $expr support in MatchExpression ... Closed
is duplicated by SERVER-2549 expression native support , find({ a... Closed
Related
related to SERVER-2507 variable field name in queries Backlog
is related to SERVER-2549 expression native support , find({ a... Closed
is related to SERVER-7623 comparison operation right hand side ... Closed
is related to SERVER-21091 Document Validation should allow comp... Closed
Backwards Compatibility: Fully Compatible
Sprint: Query 2017-10-23
Participants:

 Description   

It would be rather handy to do things like :

db.test.find({a:{$gt:b}})

In other words, be able to query based on document fields comparison. Any plans for this?



 Comments   
Comment by David Storch [ 06/Oct/17 ]

The feature described by tess.avitabile above was implemented in SERVER-30731 and will be available in upcoming version 3.6.0-rc0. The reference page for $expr is available here:

https://docs.mongodb.com/master/reference/operator/query/expr/

Comment by Tess Avitabile (Inactive) [ 05/Oct/17 ]

This can now be done using $expr, which allows you to use aggregation expressions in the match language:

> db.coll.insert({_id: 0, a: 5, b: 5})
WriteResult({ "nInserted" : 1})
> db.coll.insert({_id: 1, a: 5, b: 6})
WriteResult({ "nInserted" : 1})
> db.coll.find({$expr: {$eq: ["$a", "$b"]}})
> { "_id:" : 0, "a" : 5, "b" : 5 }

Comment by Jonathan Rezende [ 09/Oct/16 ]

weird, this seem to be something so simple. It would be very helpful with the validator option.

Comment by Remon van Vliet [ 09/Jan/12 ]

I don't think that'll work since that's would be rather ambiguous syntax. One wouldn't be able to perform an actual $gt on the string "$b" without some sort of escaping or other mechanism that allows special casing $-first strings. I haven't looked at the aggregation framework yet so I'm not sure if that has similar issues. In any case, for this particular feature request I don't think the {$gt:"$b"} approach is preferable.

EDIT: I've reviewed the aggregation framework syntax and theoretically it suffers from the same problem although I can't see any direct issues so far since the expressions/pipeline operators where it would matter do not allow field references it seems (e.g. you can't {$match:{field1:"$field2"}} by the looks of it). Obviously having to compare with the string "$field" rather than the field "field" is an edge case but that can hardly be a valid argument in favor of the current syntax. Hoping that a user doesn't do something that should be possible isn't really a sound design philosophy.

Comment by Matt Vogel [ 08/Jan/12 ]

Personally, I like the direction we're going with the aggregation framework which would look more like this:

db.test.find({a:{$gt:"$b"}})

Comment by Remon van Vliet [ 09/Mar/11 ]

By the way, the above example isn't valid JSON obviously so it would probably need an operator of some sort, e.g. : db.test.find({a:{$gt:{$field: "b"}}})

Generated at Thu Feb 08 03:00:57 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.