Mongodb复杂聚合查询排序

Question

问题是：打印一份所有住宿的列表，先按价格排序，然后按提供的便利设施数量排序。然而，有两个问题，第一个问题是price是一个以$开头的字符串，必须首先将其转换为整数才能进行正确排序，另一个问题是，我不知道如何计算每个文档的便利设施属性数量。

My JSON is like this:
[
{
"_id": "5bf31f8c856a12c73c888cee",
"index": 0,
"guid": "f99844b5-7ecd-4c90-860f-b79a742fcac1",
"isActive": true,
"registered": "2012-06-14T02:14:30 -02:00",
"host": {
"name": "Grimes",
"surname": "Terrell",
"gender": "male",
"age": 55,
"picture": "https://api.adorable.io/avatars/285/GrimesTerrell.png",
"languages": [
"spanish",
"slovenian",
"english"
],
"contact": {
"company": "QUANTALIA",
"email": "[e-mail]",
"phone": "[phone]"
},
"reviews": [
{
"reviewer": "Mae Ryan",
"date": "2015-06-01T02:41:46 -02:00",
"helpful": 8,
"kind": 1,
"responsive": 5
},
{
"reviewer": "Nixon Johnson",
"date": "2016-02-08T10:35:12 -01:00",
"helpful": 1,
"kind": 1,
"responsive": 9
},
{
"reviewer": "Dorothy Copeland",
"date": "2016-07-17T12:44:31 -02:00",
"helpful": 3,
"kind": 3,
"responsive": 4
},
{
"reviewer": "Kathleen Roth",
"date": "2014-12-26T04:38:36 -01:00",
"helpful": 10,
"kind": 3,
"responsive": 5
},
{
"reviewer": "Hallie Hart",
"date": "2016-02-14T10:21:50 -01:00",
"helpful": 9,
"kind": 4,
"responsive": 9
}
]
},
"lodging": {
"price": "$86",
"address": {
"street": "645 Louisa Street",
"city": "Fairhaven",
"state": "Nebraska",
"coordinates": [
5.439653,
30.761532
]
},
"amenities": {
"parking": true,
"petsAllowed": true,
"airConditioning": true,
"elevator": true
},
"reviews": [
{
"reviewer": "Susanne Walton",
"date": "2018-03-20T04:50:18 -01:00",
"cleanliness": 8,
"location": 1,
"food": 10
},
{
"reviewer": "Henrietta Manning",
"date": "2017-12-14T07:05:46 -01:00",
"cleanliness": 10,
"location": 5,
"food": 1
},
{
"reviewer": "Newman West",
"date": "2018-05-07T05:23:55 -02:00",
"cleanliness": 9,
"location": 10,
"food": 6
},
{
"reviewer": "Brennan Rowland",
"date": "2016-09-20T04:49:15 -02:00",
"cleanliness": 10,
"location": 1,
"food": 8
},
{
"reviewer": "Beulah Lambert",
"date": "2016-04-20T11:51:43 -02:00",
"cleanliness": 4,
"location": 8,
"food": 9
},
{
"reviewer": "Joseph Mcdowell",
"date": "2015-02-10T12:14:20 -01:00",
"cleanliness": 7,
"location": 9,
"food": 3
}
]
}
},
{
"_id": "5bf31f8c20b3acacc2194faa",
"index": 1,
"guid": "7d65ed39-13d0-4121-bacd-3b6e1c1daa17",
"isActive": true,
"registered": "2016-04-22T08:54:50 -02:00",
"host": {
"name": "Schultz",
"surname": "French",
"gender": "male",
"age": 24,
"picture": "https://api.adorable.io/avatars/285/SchultzFrench.png",
"languages": [
"spanish",
"polish"
],
"contact": {
"company": "ORBEAN",
"email": "[e-mail]",
"phone": "[phone]"
},
"reviews": [
{
"reviewer": "Prince Berg",
"date": "2017-11-10T01:18:41 -01:00",
"helpful": 5,
"kind": 5,
"responsive": 6
}
]
},
"lodging": {
"price": "$45",
"address": {
"street": "323 Polar Street",
"city": "Lutsen",
"state": "Louisiana",
"coordinates": [
30.447311,
24.791852
]
},
"amenities": {
"freeWifi": true,
"parking": true,
"petsAllowed": true,
"elevator": true,
"balcony": true
},
"reviews": [
{
"reviewer": "Mariana Ayers",
"date": "2014-04-13T08:30:02 -02:00",
"cleanliness": 9,
"location": 6,
"food": 4
},
{
"reviewer": "Cole Simmons",
"date": "2015-05-07T06:00:09 -02:00",
"cleanliness": 1,
"location": 3,
"food": 2
}
]
}
},
{
"_id": "5bf31f8c8af00ea536cb92d7",
"index": 2,
"guid": "74ef5317-4949-4259-836c-43cc05eed32c",
"isActive": true,
"registered": "2010-09-12T02:38:43 -02:00",
"host": {
"name": "Nicholson",
"surname": "Cooper",
"gender": "male",
"age": 71,
"picture": "https://api.adorable.io/avatars/285/NicholsonCooper.png",
"languages": [
"german",
"english"
],
"contact": {
"company": "COMTENT",
"email": "[e-mail]",
"phone": "[phone]"
},
"reviews": [
{
"reviewer": "Corinne Barber",
"date": "2017-01-22T08:16:59 -01:00",
"helpful": 9,
"kind": 6,
"responsive": 3
},
{
"reviewer": "Callahan Mann",
"date": "2015-08-18T03:46:07 -02:00",
"helpful": 9,
"kind": 5,
"responsive": 9
},
{
"reviewer": "Alberta Chan",
"date": "2016-07-27T01:59:55 -02:00",
"helpful": 9,
"kind": 2,
"responsive": 1
},
{
"reviewer": "Hampton Horn",
"date": "2017-08-08T04:20:22 -02:00",
"helpful": 9,
"kind": 4,
"responsive": 2
},
{
"reviewer": "Castillo Farley",
"date": "2015-07-04T12:00:38 -02:00",
"helpful": 9,
"kind": 3,
"responsive": 6
},
{
"reviewer": "Imogene Henson",
"date": "2014-12-22T04:15:35 -01:00",
"helpful": 4,
"kind": 6,
"responsive": 10
}
]
},
"lodging": {
"price": "$91",
"address": {
"street": "979 Varet Street",
"city": "Allendale",
"state": "Illinois",
"coordinates": [
24.238092,
-51.522132
]
},
"amenities": {
"freeWifi": true,
"parking": true,
"petsAllowed": true,
"airConditioning": true,
"balcony": true
},
"reviews": []
}
},
{
.
.
.

我尝试过这样的东西，但是，我对mongodb还很陌生。

db.lodging.aggregate(
[
{$project: {"substring": {$substr: ["lodging.price", 1,-1 ]}} }, // String to substring (number).
{$project: {intfield: {$toInt: substring}}}, // Integer from substring for order by.
{$project: {}} // TODO: Number of amenities.
{$sort: {intfield: -1, numamenities: -1}}
]
);

Answer 1

像这样的聚合可以做到这一点：

db.lodging.aggregate([
{
$addFields: {
"intfield": {
$toInt: {
$substr: ["$lodging.price", 1, -1]
}
},
"numamenities": {
$size: {
$objectToArray: "$lodging.amenities"
}
}
}
},
{
$sort: {
intfield: -1,
numamenities: -1
}
}
])

我使用的第一个聚合阶段是$addFields。我没有使用$project的原因是，只有$project文档中的字段会传递到下一阶段。因此，当您编写这个{$project: {"substring": {$substr: ["lodging.price", 1,-1 ]}} }时，下一阶段唯一可用的字段将是substring。为了传递文档的其余部分，最好使用$addFields。

在计算intfield时，您可能会注意到我传递的值是$lodging.price，其中$而不是lodging.price。$让MongoDB知道您想要字段lodging.price的值，而不是值为lodging.price的字符串。

对于numamenities，我使用$objectToArray将lodging.amenities字段转换为一个数组，这样就可以使用$size运算符来获取字段数。

既然intfield和numamenities已经添加到每个文档中，那么剩下的就是$sort了。