How to query two collections that have a "one-to-many" relationship in MongoDB?

Question

We have two collections: "houses" and "events". Each document in the houses collection contains an "events" field with an array of id's that refer to events (a "one-to-many" relationship). The array can be empty.

"House" schema:

const mongoose = require('mongoose');

const Schema = mongoose.Schema;

const HouseSchema = new Schema({
  name: String,
  district: String,
  locality: String,
  date: {
    type: Date,
    default: Date.now
  },
  events: [{
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Event'
  }]
});

module.exports = mongoose.model('House', HouseSchema);

"Event" schema:

const mongoose = require('mongoose');

const Schema = mongoose.Schema;

const EventSchema = new Schema({
  event: String,
  details: String,
  date: {
    type: Date,
    default: Date.now
  },
  house: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'House'
  }
});

module.exports = mongoose.model('Event', EventSchema);

I need to query both "houses" and "events" and find those "houses" with referred "events" that meet all the criteria. I will give several options for queries.

Query with parameters only from "houses". Let's say there are two docs in the "houses" collection that satisfy the condition {"locality": "Facebook"}. The result should be like this:

[
  {
    "_id": "601bae8e26ed00591d571dbe",
    "name": "Facebook testing department",
    "district": "Los-Angeles",
    "locality": "Facebook",
    "events": [
      {
        "_id": "601bae8e26ed00591d571dbf",
        "event": "Testing",
        "details": "Testing software for production",
        "date": "2020-07-31T21:00:00.000Z"
      }
    ]
  },
  {
    "_id": "601bae8e26ed00591d571dbc",
    "name": "Facebook office",
    "district": "Los-Angeles",
    "locality": "Facebook",
    "events": [
      {
        "_id": "601bae8e26ed00591d571dbd",
        "event": "Conference",
        "details": "Developers conference about 12345",
        "date": "2020-07-31T21:00:00.000Z"
      }
    ]
  },
];

Query with parameters only for "events". Let's say there are two records in the "events" collection that satisfy the condition {"event": "Conference"}. The result is:

[
  {
    "_id": "601bae8e26ed00591d571dbc",
    "name": "Facebook office",
    "district": "Los-Angeles",
    "locality": "Facebook",
    "events": [
      {
        "_id": "601bae8e26ed00591d571dbd",
        "event": "Conference",
        "details": "Developers conference about 12345",
        "date": "2020-07-31T21:00:00.000Z"
      }
    ]
  },
  {
    "_id": "601bae8e26ed00591d571dba",
    "name": "Oxford",
    "district": "London",
    "locality": "Oxford",
    "events": [
      {
        "_id": "601bae8e26ed00591d571dbb",
        "event": "Conference",
        "details": "About something",
        "date": "2020-07-31T21:00:00.000Z"
      }
    ]
  },
];

Query with parameters for both "houses" and "events". If the criteria of our query is {"locality": "Facebook", "event": "Conference"}, then the result should be like this:

[  
  {
    "_id": "601bae8e26ed00591d571dbc",
    "name": "Facebook office",
    "district": "Los-Angeles",
    "locality": "Facebook",
    "events": [
      {
        "_id": "601bae8e26ed00591d571dbd",
        "event": "Conference",
        "details": "Developers conference about 12345",
        "date": "2020-07-31T21:00:00.000Z"
      }
    ]
  },
];

I have faced similar situations where it is necessary to make queries more complex than usual. But here we need to make a query - depending on the parameters - for one or two collections, and as a result, we should get "houses" with nested "events" that correspond the query parameters. I don't know MongoDB aggregation that well, would appreciate any help.

Answer 1

• Declare your inputs

let locality = req.body.locality;
let event = req.body.event;

• Initialize aggregation pipeline (replace HousesModel to your reql model name)

let p = HousesModel.aggregate();

• Check condition for locality

if (locality && locality.trim()) p.match({ locality: locality });

• $lookup with pipeline,
• declare events array into let
• match events condition in expression
• check condition if events search is available then put condition

p.lookup({
  from: "events", // replace your actual collection name if this is wrong
  let: { events: "$events" },
  pipeline: [
    {
      $match: {
        $and: [
          { $expr: { $in: ["$_id", "$$events"] } },
          (event && event.trim() ? { event: event } : {})         
        ]
      }
    }
  ],
  as: "events"
});

• make sure events is not empty

p.match({ events: { $ne: [] } });

• execute above pipeline

let result = await p.exec();

Playground

Combine above code in sequence and test.