Mongodb Aggregation=[ find-$lookup-$match-$check and $update]

Question

I am a Mongodb beginner, I have two collections(relational) in database:

vendors collection:

First, I need to search the document with email and then find the stats_ID which Expired field value is false in the subdocument and search the another collection with these stats_id**

  {
    "_id": {
      "$oid": "6229d3cfdbfc81a8777e4821"
    },
    "stats": [
      {
        "stats_ID": {
          "$oid": "62289ded8079821eb24760e0"
        },
        "New": false,
        "Expired": false
      },
      {
        "stats_ID": {
          "$oid": "6228a252fb4554dd5c48202a"
        },
        "New": false,
        "Expired": false
      },
      {
        "stats_ID": {
          "$oid": "622af1c391b290d34701af9f"
        },
        "New": false,
        "Expired": false
      }
    ],
    "email": "man2996@gmail.com"
  }

Data collection:

the stats_ID(from first collection result) should match with below document(for example, I added only one doc) with the field _id and check the creation date is more than todays date. if the creation date is greater than todays date of the data collection _,then the Expired value of stats_id(in vendor collection) should change to true.

[
  {
    "_id": {
      "$oid": "62289ded8079821eb24760e0"
    },
    "Main_ID": "JB-456765",
   
     "Expired": {
      "$date": "2022-03-15T18:30:00Z"
    }
  }
]

I have tried with many methods but not getting the desired result

Answer 1

I think I understand how you want to update your vendors docs. Here's one way to do it.

db.vendors.aggregate([
  { // match on email and some Expired is false
    "$match": {
      "email": "man2996@gmail.com",
      "stats.Expired": false
    }
  },
  { // lookup data with matching _id
    "$lookup": {
      "from": "data",
      "localField": "stats.stats_ID",
      "foreignField": "_id",
      "as": "data"
    }
  },
  { "$set": { // rebuild stats
      "stats": {
        "$map": {
          "input": "$stats",
          "as": "stat",
          "in": { // is stats_ID somewhere in data?
            "$cond": [
              { "$in": [ "$$stat.stats_ID", "$data._id" ] },
              { // Yes!
                "$mergeObjects": [
                  "$$stat", // keep everything
                  {
                    "$cond": [ // check dates
                      {
                        "$gt": [
                          "$$NOW",
                          {
                            "$getField": {
                              "field": "Expired",
                              "input": {
                                "$first": {
                                  "$filter": {
                                    "input": "$data",
                                    "as": "datum",
                                    "cond": {
                                      "$eq": [ "$$stat.stats_ID", "$$datum._id" ]
                                    }
                                  }
                                }
                              }
                            }
                          }
                        ]
                      },
                      { // data Expired > NOW
                        "Expired": true
                      },
                      null  // data Expired <= NOW
                    ]
                  }
                ]
              },
              "$$stat"  // No data._id==stats_ID, so keep
            ]
          }
        }
      }
    }
  },
  { // don't need data anymore
    "$unset": [ "data" ]
  },
  { // update doc
    "$merge": { "into": "vendors" }
  }
])

Try it on mongoplayground.net.