Question 116020 in Mongodb Question posted in
The official documentation can be found here.

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

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 🙂

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search