CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
learner
learner

Reputation: 1023

Querying Array of Embedded Documents in MongoDB based on Range

How can I query array of embedded document in mongodb. 

{
 _id: 1,
 zipcode: "63109",
 students: [
              { name: "john", school: 102, age: 14 },
              { name: "jess", school: 102, age: 11 },
              { name: "jeff", school: 108, age: 15 }
           ]
}
{
 _id: 2,
 zipcode: "63110",
 students: [
              { name: "ajax", school: 100, age: 7 },
              { name: "achilles", school: 100, age: 8 },
           ]
}
{
 _id: 3,
 zipcode: "63109"
}

Let's say for the above data how will retrieve only those lines where age>=7 and age<=10. For id_1 only the row with age 10 should be returned. Both the rows in id_2 and id_3. Also, id_4 has no field called students. So, I DO NOT, want to see it in the output.

Edit:- The output I get looks something like this when I do a filter. But I DO NOT want the row that has "None". The last document _id: 3 does not have the field students but filtering is done on students, so the output has a "None" corresponding to it.

I wish to handle 2 cases:

  1. No filtering should be applied where the array "students" don't exist under an id.
  2. Array students exists but is empty []

Both these cases end up in the output if simply filtered.


{'_id': ObjectId('5cdaefd393436906b016ddb4'),
 'students': [{'name': ajax,
               'school': '100',
               'age': '7'},
              {'name': achilles,
               'school': '100',
               'age': '8'}],
{'_id': ObjectId('5cdaefd393436906b016ddb3'), 'students': None}

Note: I am using a different but identical data set, so the output is not exact, but I hope you get the idea. The 'student' is an embedded document which may or may not be present in all documents.

Edit : Finally I used unwind to achieve what I wanted.

Upvotes: 2

Views: 111

Answers (1)

Ashh
Ashh

Reputation: 46441

You can use below aggregation

db.collection.aggregate([
  { "$match": { "$expr": { "$gte": [{ "$size": { "$ifNull": ["$students", []] } }, 1] }}},
  { "$addFields": {
    "students": {
      "$filter": {
        "input": { "$ifNull": ["$students", []] },
        "cond": {
          "$and": [
            { "$gte": ["$$this.age", 7] },
            { "$lte": ["$$this.age", 10] }
          ]
        }
      }
    }
  }}
])

Upvotes: 1

Related Questions