0

1. Finding an $and match only if it occurs within the same array element

Let us assume we have these two documents in a mongoDB:

{_id: 1, people: [{height: 10, age: 10}, {height: 5, age: 5}]}
{_id: 2, people: [{height: 10, age: 5},  {height: 5, age: 10}]}

And we want to match every document where there is at least 1 person whose height and age are both >= 10. My current query is similar to {$and: [{people.height: {$gte, 10}}, {people.age: {$gte: 10}}]}

However, this is returning both ID 1 and 2, when only id 1 contains a single person who is >=10 for both.

2. Same problem as above, but that list is nested within a second list. Only ID's should be returned if there is at least one person who is both >= 10 age and height.

Lets assume we have these two documents in a mongoDB:

{_id: 1, families: [people: [{height: 10, age: 10}, {height: 5, age: 5}], people: [{height: 0, age: 0}, {height: 0, age: 0}]]}
{_id: 2, families: [people: [{height: 10, age: 5}, {height: 0, age: 0}], people: [{height: 5, age: 10}, {height: 0, age: 0}]]}

How could I construct a query to only match id 1?

3. Building the correct index for this type of query

Currently I have a compound index equivalent to {families.people.height, families.people.age} but I believe this will not be optimized for the query I should be using. Should the index instead be {families.people}?

Note: hypothetically, this query is being run on a mongoDB with ~700,000,000 documents

Kyle
  • 31
  • 3
  • 1
    The documents in the case 2 are not valid docs. For the first case `{$elemMatch: {height: {$gte: 10}, age: {$gte: 10}}}` There is an [example](https://docs.mongodb.com/manual/reference/operator/query/elemMatch/#array-of-embedded-documents) in the [`$elemMatch`'s doc](https://docs.mongodb.com/manual/reference/operator/query/elemMatch/) – styvane Nov 07 '19 at 21:21

2 Answers2

0

So, you did not clarify the requirements for data output format. Assuming the data schema returned from the aggregation does not need to be identical to the documents in the collection you could use $unwind.

Example:

db.coll.aggregate([
  { $unwind: "$people" },
  { $addFields: {
      isHeightMatch: { $gte: [ "$people.height", 10 ] },
      isAgeMatch: { $gte: [ "$people.age", 10 ] }
    }
  },
  { $match: {
      $and: [
        { isHeightMatch: true },
        { isAgeMatch: true }
      ]
    } 
  },
  { $project: {
      "isHeightMatch": 0,
      "isAgeMatch": 0
    }
  }
])

Output:

{ "_id" : 1, "people" : { "height" : 10, "age" : 10 } }
barrypicker
  • 9,740
  • 11
  • 65
  • 79
0

You can use MongoDB $elemMatch to achieve more specific array element matches.

In order to get at least 1 person whose height and age are both >= 10, you can use this query:

{ people: { $elemMatch: { $and: [ {height: {$gte: 10} }, { age: {$gte: 10} } ]  } } }

The same thing applies to nested arrays. If the people array is nested in a families array, you can use this query:

{ 'families.people': { $elemMatch: { $and: [ {height: {$gte: 10} }, { age: {$gte: 10} } ]  } } }

I hope that helps.

Tunmee
  • 2,483
  • 1
  • 7
  • 13