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

Mongodb – mongo query for a one-to-many collection where all records has to match the condition and get an unique record

Employee has multiple employeeActions, the employeeActions data looks like this:

[
  
  {
    "email": "[email protected]",
    "companyRegNo": 105,
    "event": {
      "created": ISODate("2022-09-16T06:42:04.387Z"),
      "desc": "COMPLETED_APPLICATIONS",
      "note": "Direct apply"
      
    }
  },
  {
    "email": "[email protected]",
    "companyRegNo": 105,
    "event": {
      "created": ISODate("2022-09-20T06:42:42.761Z"),
      "desc": "ASKED_TO_REVIEW",            
    }
  },

  {
    "email": "[email protected]",
    "companyRegNo": 227,
    "event": {
      "created": ISODate("2022-09-16T06:42:04.387Z"),
      "desc": "COMPLETED_APPLICATIONS",
      "note": "Direct apply",
      
    }
  },
  {
    "email": "[email protected]",
    "companyRegNo": 227,
    "event": {
      "created": ISODate("2022-09-28T06:42:42.761Z"),
      "desc": "ASKED_TO_REVIEW",            
    }
  },

  {
    "email": "[email protected]",
    "companyRegNo": 157,
    "event": {
      "created": ISODate("2022-09-16T06:42:04.387Z"),
      "desc": "COMPLETED_APPLICATIONS",
      "note": "Direct apply",      
    }
  },
  {
    "email": "[email protected]",
    "companyRegNo": 201,
    "deleted": true,
    "event": {
      "created": ISODate("2022-09-15T06:42:42.761Z"),
      "desc": "COMPLETED_APPLICATIONS",
      
      
    }
  },
]

I need to write an aggregation query to get all email ids where the employee action of the user
– Does not have an ASKED_TO_REVIEW event created before ‘2022-09-25’
– deleted is either false or does not exist

The out put should have only

 {"email": "[email protected]"}
 {"email": "[email protected]"}

The below match and project query did not work

db.collection.aggregate([
  {
    "$match": {
      "$and": [
        {
          "deleted": {
            "$ne": true
          }
        },
        {
          "$or": [
            {
              "$and": [
                {
                  "event.name": {
                    "$eq": "ASKED_TO_REVIEW"
                  }
                },
                {
                  "event.created": {
                    "$lt": ISODate("2022-09-25")
                  }
                }
              ]
            },
            {
              "event.name": {
                "$ne": "ASKED_TO_REVIEW"
              }
            }
          ]
        }
      ]
    }
  },
  {
    "$project": {
      "email": 1,
      "_id": 0
    }
  }
])

How do i go about this?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Figured out the working query. After grouping by email, $elemMatch needs to be used for the and condition between "event.desc" and "event.created"

    db.collection.aggregate([
  {
    "$group": {
      "_id": "$email",
      "field": {
        "$push": "$$ROOT"
      }
    }
  },
  {
    "$match": {
      "$and": [
        {
          "field.deleted": {
            "$ne": true
          }
        },
        {
          "$or": [
            {
              "field": {
                "$elemMatch": {
                  "event.desc": "ASKED_TO_REVIEW",
                  "event.created": {
                    "$lt": ISODate("2022-09-25")
                  }
                }
              }
            },
            {
              "field.event.desc": {
                "$ne": "ASKED_TO_REVIEW"
              }
            }
          ]
        }
      ]
    }
  },
  {
    "$project": {
      email: "$_id",
      "_id": 0
    }
  }
])

    Playground Link


  2. 0

    You need to group the events by email and then apply your filtering logic to those groups, something like this:

    db.collection.aggregate([
  {
    "$group": {
      "_id": "$email",
      "field": {
        "$push": "$$ROOT"
      }
    }
  },
  {
    "$match": {
      $expr: {
        "$eq": [
          0,
          {
            "$size": {
              "$filter": {
                "input": "$field",
                "as": "item",
                "cond": {
                  "$or": [
                    {
                      "$and": [
                        {
                          "$eq": [
                            {
                              "$getField": {
                                "field": "desc",
                                "input": "$$item.event"
                              }
                            },
                            "ASKED_TO_REVIEW"
                          ]
                        },
                        {
                          "$lt": [
                            {
                              "$getField": {
                                "field": "created",
                                "input": "$$item.event"
                              }
                            },
                            ISODate("2022-09-25")
                          ]
                        }
                      ]
                    },
                    {
                      "$eq": [
                        {
                          "$getField": {
                            "field": "deleted",
                            "input": "$$item"
                          }
                        },
                        true
                      ]
                    }
                  ]
                }
              }
            }
          }
        ]
      }
    }
  },
  {
    "$project": {
      email: "$_id",
      "_id": 0
    }
  }
])

    Playground link.

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

Back To Top
Search