Mongo DB query to match a field1 and loop thru another field2 and get output as a single array with all fields of field2

Question

Need help with mongo db query

Mondo db query - search for parents with state good and children with state bad or missing. output should be an array of all the children with state bad or missing from parents with good state

Below is the JSON list

[
    {
        "name": "parent-a",
        "status": {
            "state": "good"
        },
        "children": [
                "child-1", 
                "child-2"   
        ]
    },
    {
        "name": "child-1",
        "state": "good",
        "parent": "parent-a"
    },
    {
        "name": "child-2",
        "state": {},
        "parent": "parent-a"
    },
    {
        "name": "parent-b",
        "status": {
            "state": "good"
        },
        "children": [
            "child-3", 
            "child-4" 

        ]
    },
    {
        "name": "child-3",
        "state": "good",
        "parent": "parent-b"
    },
    {
        "name": "child-4",
        "state": "bad",
        "parent": "parent-b"
    },
    {
        "name": "parent-c",
        "status": {
            "state": "bad"
        },
        "children": [
            "child-5", 
            "child-6" 

        ]
    },
    {
        "name": "child-5",
        "state": "good",
        "parent": "parent-c"
    },
    {
        "name": "child-6",
        "state": "bad",
        "parent": "parent-c"
    }

]

Expected output

    "children": [
        {
            "name": "child-2",
            "state": {}
        },
        {
            "name": "child-4",
            "state": "bad"
        }
    ]

Any inputs would be appreciated. Thanks in advance :)

Answer 1

One option is to use $lookup* for this:

db.collection.aggregate([
  {$match: {state: {$in: ["bad", {}]}}},
  {$lookup: {
      from: "collection",
      localField: "parent",
      foreignField: "name",
      pipeline: [
        {$match: {"status.state": "good"}}
      ],
      as: "hasGoodParent"
  }},
  {$match: {"hasGoodParent.0": {$exists: true}}},
  {$project: {name: 1, state: 1, _id: 0}}
])

See how it works on the playground example

*If your mongoDB version is lower than 5.0 you need to change the syntax a bit. Drop the localField and foreignField of the $lookup and replace with let and equality match on the pipeline