I have this data which is in multiple email format, I have many test users for which I want to match with one and get that user's Date to sort my data. The data is:
[
{
_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"
},
{
email_id: "[email protected]",
name: "test 2",
date: "2021-10-01 13:00:00"
}
]
},
{
_id: "60642127b982sa55299q674444b",
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: null,
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"
}
]
},
{
_id: "60642127b982sa55299q674444c",
subject: "Email 3",
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:15:00"
}
],
cc: null,
bcc: null
},
{
_id: "60642127b982sa55299q674444d",
subject: "Email 4",
body: "Body 2",
to: [
{
email_id: "[email protected]",
name: "test 1",
date: "2021-10-01 12:10:00"
},
{
email_id: "[email protected]",
name: "test 3",
date: "2021-10-01 13:10:00"
}
],
cc: null,
bcc: [
{
email_id: "[email protected]",
name: "test 2",
date: "2021-10-01 12:10:00"
},
{
email_id: "[email protected]",
name: "test 6",
date: "2021-10-01 17:10:00"
}
]
},
{
_id: "60642127b982sa55299q674444e",
subject: "Email 5",
body: "Body 2",
to: [
{
email_id: "[email protected]",
name: "test 1",
date: "2021-10-01 12:15:00"
},
{
email_id: "[email protected]",
name: "test 3",
date: "2021-10-01 14:10:00"
}
],
cc: [
{
email_id: "[email protected]",
name: "test 2",
date: "2021-10-01 16:50:00"
},
{
email_id: "[email protected]",
name: "test 5",
date: "2021-10-01 16:10:00"
}
],
bcc: null
}
]
I am looking for a mongodb query to fetch my userID ([email protected]) and sort it based on [email protected]'s date. I want my result to be sorted based on date in ascending order as Email 4, Email 1, Email 2, Email 3,Email 5.
[
{
"_id": null,
"uniqueValues": {
"_id": "60642127b982sa55299q674444d",
"body": "Body 4",
"concat": {
"date": "2021-10-01 12:10:00",
"email_id": "[email protected]",
"name": "test 2"
},
"subject": "Email 4"
}
},
{
"_id": null,
"uniqueValues": {
"_id": "60642127b982sa55299q674444a",
"body": "Body 1",
"concat": {
"date": "2021-10-01 13:00:00",
"email_id": "[email protected]",
"name": "test 2"
},
"subject": "Email 1"
}
},
{
"_id": null,
"uniqueValues": {
"_id": "60642127b982sa55299q674444b",
"body": "Body 2",
"concat": {
"date": "2021-10-01 13:10:00",
"email_id": "[email protected]",
"name": "test 2"
},
"subject": "Email 2"
}
},
{
"_id": null,
"uniqueValues": {
"_id": "60642127b982sa55299q674444c",
"body": "Body 3",
"concat": {
"date": "2021-10-01 13:15:00",
"email_id": "[email protected]",
"name": "test 2"
},
"subject": "Email 3"
}
},
{
"_id": null,
"uniqueValues": {
"_id": "60642127b982sa55299q674444e",
"body": "Body 5",
"concat": {
"date": "2021-10-01 16:50:00",
"email_id": "[email protected]",
"name": "test 2"
},
"subject": "Email 5"
}
}
]
I have used this query,I'm not getting the proper results. some of the documents are skipped. If anyone has any idea, please respond.
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]"
}
},
])
CodePudding user response：
The $concatArrays operator requires all of its inputs to be arrays.
From the docs:
If any argument resolves to a value of null or refers to a field that is missing, $concatArrays returns null.
For those documents where the cc field is null, the result of $concatArrays assigned to the concat field will be null.
This means that the field concat.email_id will not exist, and therefore will not match.
You might use the $ifNull operator to replace any null values with an empty array, like
{$ifNull: [ "$cc", [] ]}
CodePudding user response：
Hope , you have expected the following aggregation. I have added / modified few stages with your pipeline.
$matchto eliminate unwated documents$ifNullhelps what we need to do when variables is null, So I passed[]empty array if variable is null$concatArraysto concat all 3 arrays$unwindto deconstruct the array$groupto reconstruct the array, but without any duplications. Because I added 3 conditions to remove duplicate inside _id$sortto sort the documents
Here is the code
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: [
{ $ifNull: [ "$to", [] ] },
{ $ifNull: [ "$cc", [] ] },
{ $ifNull: [ "$bcc", [] ] }
]
}
}
},
{ "$unwind": "$concat" },
{ "$match": { "concat.email_id": "[email protected]" } },
{
"$group": {
"_id": { _id: "$_id", date: "$concat.date", email: "$concat.email_id" },
"body": { "$first": "$body" },
"concat": { "$first": "$concat" },
"subject": { "$first": "$subject" }
}
},
{ "$sort": { "concat.date": 1 } },
{ $addFields: { _id: "$_id._id" } }
])
Working Mongo playground