I need to filter a multi-level nested array in MongoDB. The schema is as follows,
{
"_id": "1234",
"array1": [
{
"id": "a11",
"array2": [
{
"id": "a21",
"array3": [
{
"id": "a31",
"status": "done"
},
{
"id": "a32",
"status": "pending"
}
]
}
]
}
]
}
The required output must filter array3 with condition status=done. Which is the best possible method to achieve the same?
CodePudding user response:
Use $map
to iterate array1
and array2
, use $filter
to filter array3
. Finally compare array3
with empty array for document matching.
db.collection.aggregate([
{
"$addFields": {
"array1": {
"$map": {
"input": "$array1",
"as": "a1",
"in": {
id: "$$a1.id",
array2: {
"$map": {
"input": "$$a1.array2",
"as": "a2",
"in": {
id: "$$a2.id",
array3: {
"$filter": {
"input": "$$a2.array3",
"as": "a3",
"cond": {
$eq: [
"$$a3.status",
"done"
]
}
}
}
}
}
}
}
}
}
}
},
{
$match: {
"array1.array2.array3": {
$ne: []
}
}
}
])
Here is the Mongo playground for your reference.
CodePudding user response:
Query
- almost the same as @ray query
$mergeObjects
allows us to not write the fields by hand($setField can be used also if mongoDB5 ) (if instead of id you also have 10 fields, this will work without changing the query)
aggregate(
[{"$set":
{"array1":
{"$map":
{"input": "$array1",
"as": "a1",
"in":
{"$mergeObjects":
["$$a1",
{"array2":
{"$map":
{"input": "$$a1.array2",
"as": "a2",
"in":
{"$mergeObjects":
["$$a2",
{"array3":
{"$filter":
{"input": "$$a2.array3",
"as": "a3",
"cond": {"$eq": ["$$a3.status", "done"]}}}}]}}}}]}}}}}])