我有多個電子郵件格式的資料，我有許多測驗用戶，我想為其匹配一個并獲取該用戶的日期來對我的資料進行排序。資料是：
[
{
_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
}
]
我正在尋找一個 mongodb 查詢來獲取我的用戶 ID ([email protected]) 并根據 [email protected] 的日期對其進行排序。我希望我的結果根據日期按電子郵件 4、電子郵件 1、電子郵件 2、電子郵件 3、電子郵件 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"
}
}
]
我使用過這個查詢，但沒有得到正確的結果。一些檔案被跳過。如果有人有任何想法，請回復。
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]"
}
},
])
uj5u.com熱心網友回復：
在$concatArrays操作者需要的所有的它的輸入為陣列。
從檔案：
如果任何引數決議為 null 值或參考缺失的欄位，則 $concatArrays 回傳 null。
對于cc欄位為空的檔案，$concatArrays分配給該concat欄位的結果將為空。
這意味著該欄位concat.email_id將不存在，因此將不匹配。
您可以使用$ifNull運算子將任何空值替換為空陣列，例如
{$ifNull: [ "$cc", [] ]}
uj5u.com熱心網友回復：
希望，您已經預料到以下聚合。我在您的管道中添加/修改了幾個階段。
$match消除不需要的檔案$ifNull當變數為空時幫助我們需要做的事情，所以如果變數為空，我傳遞了[]空陣列$concatArrays連接所有 3 個陣列$unwind解構陣列$group重建陣列，但沒有任何重復。因為我添加了3個條件來洗掉_id里面的重復項$sort對檔案進行排序
這是代碼
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" } }
])
作業Mongo 游樂場
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/314294.html
標籤：数组 MongoDB 排序 mongodb-查询