Range query on a subdocument array property

Question

I have a collection of recipes that have servings array. Each serving has a numeric energy property. I need to select all recipes with at least one serving that has energy that falls into the given range.

For example I have 2 recipes:

{
  title: 'Greek salad',
  servings: [
    {
      energy: 100
    },
    {
      energy: 150
    }
  ]
},
{
  title: 'Smashed potatoes',
  servings: [
    {
      energy: 150
    },
    {
      energy: 200
    }
  ]
}

I am looking for a query that would match only Greek salad given the range is 90..110, and both recipes for 140..160 range.

The best I came up with:

db.recipes.find({$and: [
    {'servings.energy' : {$gt: 90}}, 
    {'servings.energy' : {$lt: 110}}
   ]}

matches 2 results, which is not what is expect.

PS. There is a 1M+ records in the collections and I'm looking for a query that would run entirely out of index. Maybe I could change the data structure somehow to satisfy my needs?

Is the following result correct for the range 140..160? { title: 'Greek salad', servings: [ { energy: 150 } ] }, { title: 'Smashed potatoes', servings: [ { energy: 150 } ] } — Volodymyr Synytskyi
– Volodymyr Synytskyi, Commented Dec 30, 2015 at 18:54
@VolodymyrSynytskyi sure, but the query should perform as fast as possible. There's a million of records. — Dziamid
– Dziamid, Commented Dec 31, 2015 at 6:55
Possible duplicate of Retrieve only the queried element in an object array in MongoDB collection — Sede
– Sede, Commented Dec 31, 2015 at 16:37

Dziamid · Accepted Answer · 2016-01-18 15:17:52Z

1

You can do it with the mongodb $filter operator:

db.getCollection('recipes').aggregate([
  {$project: {
    recipe: "$$ROOT",
    servings: {$filter: {
        input: "$recommendedServings",
        as: "serving",
        cond: {
            $and: [{$gt: ['$$serving.energy', 400]}, 
                   {$lt: ['$$serving.energy', 410]}]
        } 
     }}
   }},
   {$match: {"servings.0": {$exists: true}}}
 ])

edited Jan 18, 2016 at 15:17

Dziamid

11.7k13 gold badges73 silver badges108 bronze badges

answered Dec 31, 2015 at 12:39

Volodymyr Synytskyi

4,0751 gold badge18 silver badges19 bronze badges

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

Comments

Isaiah4110 · Accepted Answer · 2015-12-31 15:01:04Z

1

You can use the following aggregation query to get the result that you are expecting:

    db.testso.aggregate([
                  {$match: { "servings.energy": { $gt: 90, $lt:110 }}},   
                  {$unwind:"$servings"},
                  {$match: { "servings.energy": { $gt: 90, $lt:110 }}}
]);

First match the records based on your criteria - this will take both the records from your subdocument.
Unwind the records now based on the servings array.
Now match the records based on your criteria again.

edited Dec 31, 2015 at 15:01

answered Dec 31, 2015 at 1:42

Isaiah4110

10.2k2 gold badges46 silver badges59 bronze badges

3 Comments

Dziamid Over a year ago

Cannot afford unwinding since i'm having like a half of a million of records

Isaiah4110 Over a year ago

Updated the query, check again now.

Dziamid Over a year ago

Good try, but the first match actually matches all records in my case, so it is essentially the same as unwinding the whole collection

Collectives™ on Stack Overflow

Range query on a subdocument array property

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related