CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
pavolve
pavolve

Reputation: 225

Count and match related documents in one query

I have two collections: Tests and Reports. They are related and Reports have field test with Test.id. Each Test document have testers_count field.

Also I have written query should returns only Tests with testers_count greater or equal than count of related Reports docs.

db.test.aggregate([
    {
      $lookup:
        {
          from: 'reports',
          localField: '_id',
          foreignField: 'test',
          as: 'reportsList'
        }
    },
    {
       $match: {
        "settings.testers_count": {$gte: {$size: "$reportsList"}}
       }
    }
])

The problem is the code does not work nor returns any error.

Upvotes: 2

Views: 81

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50406

Pretty sure that you actually mean:

db.test.aggregate([
  { "$lookup": {
    "from": "reports",
    "localField": "_id",
    "foreignField": "test",
    "as": "reportsList"
  }},
  { "$redact": {
    "$cond": {
      "if": { 
        "$gte": [ 
            "$settings.testers_count",
            { "$size": "$reportsLIst" }
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

Presuming that $settings.testers_count actually resolves to a "singular" property, and is not the member of an "array".

Which uses $redact instead and the "logical" form of $gte for the evaluation.

Also the $size operator can only be used in such an evaluation expression as $redact allows. The $match operator is really just the same in concept and operator syntax to .find(), in that it performs a "query", and can really only use the "query operators" and no calculated logical expressions.

This is what $redact is actually for. It's a "logical expression" form of $match

Upvotes: 1

Related Questions