javajsonmongodbbson

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


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:

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.


Solution

  • 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").