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, Dec 30 '15 at 18:54
@VolodymyrSynytskyi sure, but the query should perform as fast as possible. There's a million of records. — Dziamid, Dec 31 '15 at 06:55
Possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — styvane, Dec 31 '15 at 16:37

Isaiah4110 · Answer 1 · 2015-12-31T15:01:04.057

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 '15 at 15:01

answered Dec 31 '15 at 01:42

Isaiah4110

9,855
1
40
56

Cannot afford unwinding since i'm having like a half of a million of records – Dziamid Dec 31 '15 at 06:24
Updated the query, check again now. – Isaiah4110 Dec 31 '15 at 15:01
Good try, but the first match actually matches all records in my case, so it is essentially the same as unwinding the whole collection – Dziamid Jan 18 '16 at 15:03

score 1 · Accepted Answer · edited Jan 18 '16 at 15:17

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}}}
 ])

Range query on a subdocument array property

2 Answers2