Select documents in collection that match foreign key value-CodePudding

I want to use MongoDB aggregate to grab some documents in collection Events that reference the collection Program with the constraint of Program.type

Events

{
   _id: ObjectId,
   programId: ObjectId
}

Programs

{
   _id: ObjectId,
   type: "Type A"
}

The pseudo sql-like query would be like select * from events where event.id = 1234 and where program.type = "Type A"

I've got this and I have no idea what I'm doing.

const pipeline = [
  {
    $match: {_id: id}
  },
  {
    $lookup: {
        from: 'programs',
        localField: '_id',
        foreignField: 'programId',
        as: 'program'
    }
  },
  {
    $unwind: '$program'
  },
  {
    $match: {'program.type': 'Type A'}
  }
]

I actually thought this worked but it failed when I tried different types.

CodePudding user response：

If I'm not wrong, this query:

select * from events where event.id = 1234 and where program.type = "Type A"

Is this query:

db.events.aggregate([
  {
    "$match": {
      "_id": 1
    }
  },
  {
    "$lookup": {
      "from": "programs",
      "as": "programs",
      "let": {
        "programId": "$programId"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$eq": [
                "$type",
                "A"
              ]
            }
          }
        }
      ]
    }
  }
])

Where:

select * -> is get all fields by default in mongo db
from events -> db.events.aggregate
where event.id = 1234 -> is the $match
program.type = "Type A" -> $lookup with pipeline where {"$eq": ["$type","A"]}

Example here