如何在MongoDB中使用条件语句进行排序？

huangapple
go评论114阅读模式
英文:

How to use conditional statement for sort in Mongodb

问题

让我用一个例子来说明，假设我的集合是这样的：

  1. products = [
  2.     {
  3.         id: 1,
  4.         name: "a",
  5.         offer: true,
  6.         expiryDate: "23-03-2022"
  7.     },
  8.     {
  9.         id: 2,
  10.         name: "b",
  11.         offer: false, //没有优惠
  12.     },
  13.     {
  14.         id: 3,
  15.         name: "c",
  16.         offer: true,
  17.         expiryDate: "01-01-2021" //已过期
  18.     },
  19.     {
  20.         id: 4,
  21.         name: "d",
  22.         offer: true,
  23.         expiryDate: "01-06-2022"
  24.     },
  25. ]

我想要的结果是，优惠的商品首先按降序排列，然后是未过期的商品按降序排列，最后是剩下的商品，所以结果应该是这样的：

  1. [
  2.     {
  3.         id: 4,
  4.         name: "d",
  5.         offer: true,
  6.         expiryDate: "01-06-2022"
  7.     },
  8.     {
  9.         id: 1,
  10.         name: "a",
  11.         offer: true,
  12.         expiryDate: "23-03-2022"
  13.     },
  14.     {
  15.         id: 3,
  16.         name: "c",
  17.         offer: true,
  18.         expiryDate: "01-01-2021"
  19.     },
  20.     {
  21.         id: 2,
  22.         name: "b",
  23.         offer: false,
  24.     },
  25. ]
英文:

let me illustrate with an example,
suppose my collection is like this,

  1. products = [
  2.     {
  3.         id: 1,
  4.         name: "a",
  5.         offer: true,
  6.         expiryDate: "23-03-2022"
  7.     },
  9.     {
  10.         id: 2,
  11.         name: "b",
  12.         offer: false,//no offer
  14.     },
  15.     {
  16.         id: 3,
  17.         name: "c",
  18.         offer: true,
  19.         expiryDate: "01-01-2021"//already expired
  20.     },
  21.     {
  22.         id: 4,
  23.         name: "d",
  24.         offer: true,
  25.         expiryDate: "01-06-2022"
  26.     },
  27. ]

I want here, the offered items comes first in descending order,
it should not expire and then the remaining item should come in
descending order so result would be like this

  1.    [
  4.     {
  5.         id: 4,
  6.         name: "d",
  7.         offer: true,
  8.         expiryDate: "01-06-2022"
  9.     },
  10.     {
  11.         id: 1,
  12.         name: "a",
  13.         offer: true,
  14.         expiryDate: "23-03-2022"
  15.     },
  16.     {
  17.         id: 3,
  18.         name: "c",
  19.         offer: true,
  20.         expiryDate: "01-01-2021"
  21.     },
  23.     {
  24.         id: 2,
  25.         name: "b",
  26.         offer: false,
  28.     },
  31. ]

答案1

得分: 2

  1. db.collection.aggregate([
  2.   {
  3.     "$match": {}
  4.   },
  5.   {
  6.     "$set": {
  7.       "offer": {
  8.         "$cond": {
  9.           "if": {
  10.             "$lt": [
  11.               {
  12.                 "$toDate": "$expiryDate"
  13.               },
  14.               "$$NOW"
  15.             ]
  16.           },
  17.           "then": false,
  18.           "else": true
  19.         }
  20.       },
  21.       "expiryDate": {
  22.         "$toDate": "$expiryDate"
  23.       }
  24.     }
  25.   },
  26.   {
  27.     "$sort": {
  28.       "expiryDate": -1,
  29.       "offer": -1
  30.     }
  31.   }
  32. ])

[mongoplayground][1]

  2. <details>
  3. <summary>英文:</summary>

db.collection.aggregate([
{
"$match": {}
},
{
"$set": {
"offer": {
"$cond": {
"if": {
"$lt": [
{
"$toDate": "$expiryDate"
},
"$$NOW"
]
},
"then": false,
"else": true
}
},
"expiryDate": {
"$toDate": "$expiryDate"
}
}
},
{
"$sort": {
"expiryDate": -1,
"offer": -1
}
}
])

  2. [mongoplayground][1]
  5.   [1]: https://mongoplayground.net/p/ZOJwpGCWjXJ
  7. </details>

huangapple
  • 本文由 发表于 2022年2月26日 20:30:04
  • 转载请务必保留本文链接：https://go.coder-hub.com/71276662.html
  • database
  • go
  • javascript
  • mongodb
  • node.js
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定