How to add embedded field with matching documents

Question

I'm using Python with pymongo to query from the database.

I have 3 different collections:

1st:

# Projects collection
{
    "_id": "A",
},
{
    "_id": "B",
},
{
    "_id": "C"
},
..

2nd:

# Episodes collection
{
    "_id": "A/Episode01",
    "project": "A",
    "name": "Episode01"
},
{
    "_id": "A/Episode02",
    "project": "A",
    "name": "Episode02"
},
{
    "_id": "B/Episode01",
    "project": "B",
    "name": "Episode01"
},
..

3rd:

# Sequences collection
{
    "_id": "A/Episode01/Sequence01",
    "project": "A",
    "episode": "Episode01",
    "name": "Sequence01"
},
{
    "_id": "A/Episode02/Sequence02",
    "project": "A",
    "episode": "Episode02",
    "name": "Sequence02"
},
{
    "_id": "B/Episode01/Sequence01",
    "project": "B",
    "episode": "Episode01",
    "name": "Sequence01"
},
..

I want to use aggregate to query project A and get all of its corresponding episodes and sequences like this:

{
    "_id": "A",
    "episodes": 
    [
        {
            "_id": "A/Episode01",
            "project": "A",
            "name": "Episode01",
            "sequences": 
            [
                {
                    "_id": "A/Episode01/Sequence01",
                    "project": "A",
                    "episode": "Episode01",
                    "name": "Sequence01"
                },
            ]
        },
        {
            "_id": "A/Episode02",
            "project": "A",
            "name": "Episode02",
            "sequences":
            [
                {
                    "_id": "A/Episode02/Sequence02",
                    "project": "A",
                    "episode": "Episode02",
                    "name": "Sequence02"
                },
            ]
        },
    ]
}

I can get as far as getting the proper episodes, but I'm not sure how to add an embed field for any matching sequences. Is it possible to do this all in a single pipeline query?

Right now my query is looking like this:

[
    {"$match": {
        "_id": "A"}
    },
    {"$lookup": {
        "from": "episodes",
        "localField": "_id",
        "foreignField": "project",
        "as": "episodes"}
    },
    {"$group": {
        "_id": {
            "_id": "$_id",
            "episodes": "$episodes"}
    }}
]

Answer 1

You can do like following

1. use $match to match the document
2. use uncorrelated queries to join two collection. But normal joining also possible as you have written. This is easier when we get some complex situations.

Mongo script is given below

[
  {
    "$match": {
      "_id": "A"
    }
  },
  {
    $lookup: {
      from: "Episodes",
      let: {
        id: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$project",
                "$$id"
              ]
            }
          }
        },
        {
          $lookup: {
            from: "Sequences",
            let: {
              epi: "$name"
            },
            pipeline: [
              {
                $match: {
                  $expr: {
                    $eq: [
                      "$episode",
                      "$$epi"
                    ]
                  }
                }
              }
            ],
            as: "sequences"
          }
        }
      ],
      as: "episodes"
    }
  }
]

Working Mongo playground

---

Update 01

Using standard lookup

[
  {
    "$match": {
      "_id": "A"
    }
  },
  {
    "$lookup": {
      "from": "Episodes",
      "localField": "_id",
      "foreignField": "project",
      "as": "episodes"
    }
  },
  {
    $unwind: "$episodes"
  },
  {
    "$lookup": {
      "from": "Sequences",
      "localField": "episodes.name",
      "foreignField": "episode",
      "as": "episodes.sequences"
    }
  },
  {
    $group: {
      _id: "$episodes._id",
      episodes: {
        $addToSet: "$episodes"
      }
    }
  }
]

Working Mongo playground