Select data where the range between two different fields contains a given number

Question

I want to make a find query on my database for documents that have an input value between or equal to these 2 fields, LOC_CEP_INI and LOC_CEP_FIM

Example: user input a number to the system with value : 69923994, then I use this input to search my database for all documents that have this value between the range of the fields LOC_CEP_INI and LOC_CEP_FIM.

One of my documents (in this example this document is selected by the query because the input is inside the range):

 {
   "_id" : ObjectId("570d57de457405a61b183ac6"),
   "LOC_CEP_FIM" : 69923999, //this field is number
   "LOC_CEP_INI" : 69900001, // this field is number
   "LOC_NO" : "RIO BRANCO",
   "LOC_NU" : "00000016",
   "MUN_NU" : "1200401",
   "UFE_SG" : "AC",
   "create_date" : ISODate("2016-04-12T20:17:34.397Z"),
   "__v" : 0
}

Alberto Trindade Tavares · Accepted Answer · 2017-07-18 11:13:11Z

11

db.collection.find( { field: { $gt: value1, $lt: value2 } } );

https://docs.mongodb.com/v3.2/reference/method/db.collection.find/ refer this mongo provide range facility with $gt and $lt .

edited Jul 18, 2017 at 11:13

Alberto Trindade Tavares

10.5k6 gold badges42 silver badges49 bronze badges

answered Jul 18, 2017 at 9:31

Bhagvat Lande

1,5103 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VonD · Accepted Answer · 2016-04-12 21:02:42Z

3

You have to invert your field names and query value.

db.zipcodes.find({
    LOC_CEP_INI: {$gte: 69923997},
    LOC_CEP_FIM: {$lte: 69923997}
});

For your query example to work, you would need your documents to hold an array property, and that each item in this prop hold a 69923997 prop. Mongo would then check that this 69923997 prop has a value that is both between "LOC_CEP_INI" and "LOC_CEP_FIM" for each item in your array prop.

Also I'm not sure whether you want LOC_CEP_INI <= 69923997 <= LOC_CEP_FIM or the contrary, so you might need to switch the $gte and $lte conditions.

edited Apr 12, 2016 at 21:02

answered Apr 12, 2016 at 20:36

VonD

5,1452 gold badges22 silver badges30 bronze badges

5 Comments

michelpm1 Over a year ago

Humm and if i dont have a array property?

VonD Over a year ago

then you just write the code example i gave. What i meant is that if you write db.zipcodes.find({array: ...}), mongo will try to match your condition on an array prop of your documents. If they don't have one, just skip it, and write the $elemMatch condition directly

michelpm1 Over a year ago

#VonD i tried like you said with your code but i receive a error Error: error: { "waitedMS" : NumberLong(0), "ok" : 0, "errmsg" : "unknown top level operator: $elemMatch", "code" : 2 }

michelpm1 Over a year ago

tried with db.zipcodes.find({ $elemMatch: { LOC_CEP_INI: {$gte: 69923997}, LOC_CEP_FIM: {$lte: 69923997} } });

VonD Over a year ago

@michelpm1 sorry, i got confused with this array thing. I've edited my answer. $elemMatch is useful if one of your props is an array. Let's say your documents as an items props and that each item has the LOC_CEP_INI and LOC_CEP_FIM props, then you would write {items: {$elemMatch: { LOC_CEP_INI: {$gte: 69923997}, LOC_CEP_FIM: {$lte: 69923997} }}}

michelpm1 · Accepted Answer · 2016-04-13 17:25:08Z

0

db.zipcodes.find( {
"LOC_CEP_INI": { "$lte": 69900002 }, "LOC_CEP_FIM": { "$gte": 69900002 } })

answered Apr 13, 2016 at 17:25

michelpm1

1771 gold badge1 silver badge12 bronze badges

Comments

Mandeep singh · Accepted Answer · 2021-08-11 13:57:50Z

0

Here is the logic use it as per the need:

 Userdb.aggregate([
    { "$match": { _id: ObjectId(session._id)}},
    { $project: {
        checkout_list: {
            $filter: {
                input: "$checkout_list",
                as: "checkout_list",
                cond: {
                    $and: [
                        { $gte: [ "$$checkout_list.createdAt", new Date(date1) ] },
                        { $lt: [ "$$checkout_list.createdAt", new Date(date2) ] }
                    ]
                }    
            }
        }
    }
}

Here i use filter, because of some reason data query on nested data is not gets succeed in mongodb

answered Aug 11, 2021 at 13:57

Mandeep singh

11 bronze badge

Collectives™ on Stack Overflow

Select data where the range between two different fields contains a given number

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related