I have the documents similar to below. What i need here is that I need to sort the documents based on "date" field in to/cc/bcc as per the user (email_id). The user email_id might present in anyone or more than one section (to/cc/bcc). Wherever user email exist, in that particular date field should be used to sort the documents. Tried with $unwind on to,cc,bcc and applied $match, $sort, it did not work properly. In simple terms, apply unwind on more than one fields and sort the document based on date field on selected user. Thank in advance.

[{
  _id: "60642127b982sa55299q674444a",
  subject:"Email 1",
  body:"Body 1",
  to : [
     {
       email_id:"[email protected]",
       name : "test 1",
       date : "2021-10-01 12:00:00"
     },
     {
       email_id:"[email protected]",
       name : "test 2",
       date : "2021-10-01 13:00:00"
     }
  ],
  cc : [
     {
       email_id:"[email protected]",
       name : "test 3",
       date : "2021-10-01 14:00:00"
     },
     {
       email_id:"[email protected]",
       name : "test 4",
       date : "2021-10-01 15:00:00"
     }
  ],
  bcc : [
     {
       email_id:"[email protected]",
       name : "test 5",
       date : "2021-10-01 16:00:00"
     },
     {
       email_id:"[email protected]",
       name : "test 6",
       date : "2021-10-01 17:00:00"
     }
  ]
},
{
  _id: "60642127b982sa55299q674444a",
  subject:"Email 2",
  body:"Body 2",
  to : [
     {
       email_id:"[email protected]",
       name : "test 1",
       date : "2021-10-01 12:10:00"
     },
     {
       email_id:"[email protected]",
       name : "test 2",
       date : "2021-10-01 13:10:00"
     }
  ],
  cc : [
     {
       email_id:"[email protected]",
       name : "test 3",
       date : "2021-10-01 14:10:00"
     },
     {
       email_id:"[email protected]",
       name : "test 4",
       date : "2021-10-01 15:10:00"
     }
  ],
  bcc : [
     {
       email_id:"[email protected]",
       name : "test 5",
       date : "2021-10-01 16:10:00"
     },
     {
       email_id:"[email protected]",
       name : "test 6",
       date : "2021-10-01 17:10:00"
     }
  ]
}]

CodePudding user response：

aggregate

db.collection.aggregate([
  {
    "$match": {
      "$or": [
        {
          "to.email_id": "[email protected]"
        },
        {
          "cc.email_id": "[email protected]"
        },
        {
          "bcc.email_id": "[email protected]"
        }
      ]
    }
  },
  {
    "$project": {
      subject: 1,
      body: 1,
      concat: {
        $concatArrays: [
          "$to",
          "$cc",
          "$bcc"
        ]
      }
    }
  },
  {
    "$unwind": "$concat"
  },
  {
    "$match": {
      "concat.email_id": "[email protected]"
    }
  },
  {
    "$sort": {
      "concat.date": -1
    }
  }
])

mongoplayground