Query child documents based off field that is only present in parent?

Question

In my MongoDB collection I have a documents that follow a parent-child structure.

Each parent doc typically has 4 fields, with the children having 3 (no group field).

parent:

{
_id: doc_123
parent_id: 123
active: true
group: A
}

children

{
id: doc_123_1
parent_id: 123
active: true
}

{
id: doc_123_2
parent_id: 123
active: true
}

I want to write a BSON query / aggregation if needed for my Java Spring project that will return all the docs that match the following fields provided by user:

• active field - this will be true or false
• group field - e.g "A"

My difficulty is that each child document is assumed to have the same value as the parent for the group field, but it is not actually in the document.

How can I write a query that will match all the parent and child documents for a certain group?

All documents are in the one collection, there are no separate collections for parent and child docs.

Answer 1

Join to parents adding a condition of group on the join:

db.collection.aggregate([
  {
    // self-join on child.parent_id -> parent.parent_id
    $lookup: {
      from: "collection",
      localField: "parent_id",
      foreignField: "parent_id",
      "pipeline": [
        {
          "$match": {
            "group": "$$group" // pass "A" as "group" parameter to the query
          }
        }
      ],
      as: "parent"
    }
  },
  {
    $match: {
      active: $$active, // pass true or false as "active" parameter to the query
      group: {
        $exists: false // exclude parents which join to themselves
      }
    }
  }
])

See live demo.

Define this query as a native query in a spring boot Repository and pass it the two named parameters (named "group" and "active").