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