How to query on collection values and referenced documents values?

[Narus_N_A]

Hello! I don’t know how correctly I formulated the question. I need to execute a query on both the values ​​of the collection and the values ​​of the referenced objects.
The original collection looks like this:

{
  "houses": [
    {
      "_id": "5fe72f0b4fd2c131bcc7dae0",
      "name": "John",
      "district": "Texas",
      "events": [
        {
          "kind": "developer",
          "group": "facebook"
        }
      ]
    },
    {
      "_id": "5fe72f0b4fd2c131bcc7dadf",
      "name": "Michael",
      "district": "Texas",
      "events": [
        {
          "kind": "advertiser",
          "group": "instagram"
        }
      ]
    },
    {
      "_id": "5fe72f0b4fd2c131bcc7dade",
      "name": "Frank",
      "district": "Washington",
      "events": [
        {
          "kind": "developer",
          "group": "school"
        }
      ]
    }
  ]
}

When executing a query that meets the condition district == "Texas" , I need to get the following result:

{
  "houses": [
    {
      "_id": "5fe72f0b4fd2c131bcc7dae0",
      "name": "John",
      "district": "Texas",
      "events": [
        {
          "kind": "developer",
          "group": "facebook"
        }
      ]
    },
    {
      "_id": "5fe72f0b4fd2c131bcc7dadf",
      "name": "Michael",
      "district": "Texas",
      "events": [
        {
          "kind": "advertiser",
          "group": "instagram"
        }
      ]
    }
  ]
}

Under this condition: kind == "developer" , get the following result:

{
  "houses": [
    {
      "_id": "5fe72f0b4fd2c131bcc7dae0",
      "name": "John",
      "district": "Texas",
      "events": [
        {
          "kind": "developer",
          "group": "facebook"
        }
      ]
    },
    {
      "_id": "5fe72f0b4fd2c131bcc7dade",
      "name": "Frank",
      "district": "Washington",
      "events": [
        {
          "kind": "developer",
          "group": "school"
        }
      ]
    }
  ]
}

And for a query that satisfies the condition: district == "Texas" && kind == "developer" , get the result:

{
  "houses": [
    {
      "_id": "5fe72f0b4fd2c131bcc7dae0",
      "name": "John",
      "district": "Texas",
      "events": [
        {
          "kind": "developer",
          "group": "facebook"
        }
      ]
    }
  ]
}

The query should be executed using mongoose inside the express route, and should be universal, processing a different set of request parameters:

router.get('/report', (req, res) => {
  let params = {}; 
  let { district, kind } = req.headers;

  if (district) params["district"] = district;
  if (kind) params["kind"] = kind;
  // Here should be the query
});

I am learning MongoDB and aggregation , but I don’t know so deeply all its functions. Please tell me how to correctly execute such a request in the traditional way? I will be very grateful!

I also put the collection on the MongoDB playground in case someone is more comfortable:
mongoplayground

[shrey_batra]

Hi @Narus_N_A, It looks like a simple query you would need to apply. The equality query is pretty straightforward and can be solved using a simple $project stage and filtering the “nested” documents in houses array using $filter operator.

Do check the examples of $filter operator and you would get an idea of how to accomplish it. Also, do remember to apply a $match stage as first aggregation step, so that you can limit the documents you want.

If you unable to solve it using the examples, do reach out again…!

[Narus_N_A]

Thank you! I used the given methods and the problem was solved.