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 :)

CodePudding user response：

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

CodePudding user response：

Here is an approach doing this all without a "$lookup" stage as performance usually suffers when involved. Basically we match all relevant children and parents and we group by the child id. if it has a parent (which means the parent has a "good" state, and a "child" which means the child has a "bad/{}" state then it's matched).

You should make sure you have the appropriate indexes to support the initial query.

Additionally I would personally recommend adding a boolean field on each document to mark wether it's a parent or a child. right now we have to use the field structure based on your input to mark this type but I would consider this a bad practice.

Another thing we did not discuss which doesn't seem possible from the current structure is recursion, can a child have children of it's own? Just some things to consider

db.collection.aggregate([
  {
    $match: {
      $or: [
        {
          $and: [
            {
              "status.state": "good"
            },
            {
              parent: {
                $exists: false
              }
            },
            {
              "children.0": {
                $exists: true
              }
            }
          ]
        },
        {
          $and: [
            {
              "state": {
                $in: [
                  "bad",
                  null,
                  {}
                ]
              }
            },
            {
              parent: {
                $exists: true
              }
            }
          ]
        }
      ]
    }
  },
  {
    $unwind: {
      path: "$children",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $addFields: {
      isParent: {
        $cond: [
          {
            $eq: [
              null,
              {
                $ifNull: [
                  "$parent",
                  null
                ]
              }
            ]
          },
          1,
          0
        ]
      }
    }
  },
  {
    $group: {
      _id: {
        $cond: [
          "$isParent",
          "$children",
          "$name"
        ]
      },
      hasParnet: {
        $sum: "$isParent"
      },
      hasChild: {
        $sum: {
          $subtract: [
            1,
            "$isParent"
          ]
        }
      },
      state: {
        "$mergeObjects": {
          $cond: [
            "$isParent",
            {},
            {
              state: "$state"
            }
          ]
        }
      }
    }
  },
  {
    $match: {
      hasChild: {
        $gt: 0
      },
      hasParnet: {
        $gt: 0
      }
    }
  },
  {
    $group: {
      _id: null,
      children: {
        $push: {
          name: "$_id",
          state: "$state.state"
        }
      }
    }
  }
])

Mongo Playground