Query data inside an array of objects where all the objects should match the given value

Working with Data
,
1

Hey Guys, I have an array of objects in my schema

[{
users: [
  {
    name: 'frank',
    age: 15,
    email: 'frank@email.com'
  },
{
    name: 'daniel',
    age: 18,
    email: 'daniel@email.com'
  },
{
    name: 'george',
    age: 18,
    email: 'george@email.com'
  }
]
},
{
users: [
  {
    name: 'dan',
    age: 19,
    email: 'dan@email.com'
  },
{
    name: 'steve',
    age: 18,
    email: 'steve@email.com'
  },
{
    name: 'chris',
    age: 21,
    email: 'chris@email.com'
  }
]
}
]

I want to query all the documents with all it’s users being 18 or above. The methods I found like

{$match:{ 'entries.age': { age:{ $gte: 18}} }}

It works, but it matches all documents with atleast one user with age 18+.

[{
users: [
  {
    name: 'frank',
    age: 15,
    email: 'frank@email.com'
  },
{
    name: 'daniel',
    age: 18,
    email: 'daniel@email.com'
  },
{
    name: 'george',
    age: 18,
    email: 'george@email.com'
  }
]
},
{
users: [
  {
    name: 'dan',
    age: 19,
    email: 'dan@email.com'
  },
{
    name: 'steve',
    age: 18,
    email: 'steve@email.com'
  },
{
    name: 'chris',
    age: 21,
    email: 'chris@email.com'
  }
]
}
]

Instead I want the query to return only the documents where all the users are 18+

[{

users: [
  {
    name: 'dan',
    age: 19,
    email: 'dan@email.com'
  },
{
    name: 'steve',
    age: 18,
    email: 'steve@email.com'
  },
{
    name: 'chris',
    age: 21,
    email: 'chris@email.com'
  }
]
}
]
2

Hi,

You can do it like this:

  • $filter - to filter user below the age of 18 in each document.
  • $size - to get the size of above filtered array.
  • $eq - to return all documents that have no elements in filtered array. That way, only documents that have all users above the age of 18 will be returned.
db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$eq": [
          {
            "$size": {
              "$filter": {
                "input": "$users",
                "cond": {
                  "$lt": [
                    "$$this.age",
                    18
                  ]
                }
              }
            }
          },
          0
        ]
      }
    }
  }
])

Working example

4 Likes
3

Thank you so much for the solution. I do have 2 questions

1-) Running the working example is returning the first object (where a user is below 18) instead I needed second objet(where all users are above 18)

2-) How efficient is this query? My collection has over 200k+ documents with each users field having 100+ users.

4

Hi @homesite_area ,

You need to use the $all operator with $elemMatch:

Example:

[{
 $match: {
  users: {
   $all: [
    {
     $elemMatch: {
      age: {
       $gt: 18
      }
     }
    }
   ]
  }
 }
}]

Thanks
Pavel

4 Likes
5

Thanks Pavel, Your solution seemed to work but on my actual application it did not. On closer look at the query you given age: { $gt: 18 } while I needed $gte. If you change that and try on the data , it would again return both the objects.

The reason $gt worked was all the users in first object were less than or equal to 18 so your query excluded that object. But as soon as $gte is used , it finds atleast one user in object one and returns it too.

6

Hmm I see,

Try the following:

db.collection.aggregate([
  {
    $match: {
      users: {
        $not: {
          $all: [
            {
              $elemMatch: {
                age: {
                  $lt: 18
                }
              }
            }
          ]
        }
      }
    }
  }
])
3 Likes
7

Hi @homesite_area,

Sorry, just instead of $gt use $eq. I updated my answer.

When it comes to performance, you should create an index on users.age property.

P.S. @Pavel_Duchovny solution is also great, so you should check his solution too.

1 Like
8

It worked! @Pavel_Duchovny Thank you so much.

1 Like
9

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.