在MongoDB中的嵌入式集合中排序
在MongoDB中的嵌入式集合中排序
提问于 2016-08-10 20:42:02
我使用的是MongoDb 3.2.8。下面是我的测试集合，其中包含2个文档，其中存储了一个银行帐户的余额信息。两个余额(期末余额和可用余额分别针对每个账户的两种货币英镑和欧元进行存储。
/* 1 */
{
"_id" : "100001-AT611904300234101001",
"agref" : "100001",
"acref" : "AT611904300234101001",
"bal" : [
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLAV"
}
},
"amt" : {
"value" : "6452",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "6452",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLAV"
}
},
"amt" : {
"value" : "5727.8275199999998221755959093570709228515625",
"ccy" : "GBP"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "5727.8275199999998221755959093570709228515625",
"ccy" : "GBP"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
}
]
}
/* 2 */
{
"_id" : "100001-AT522904300234201001",
"agref" : "100001",
"acref" : "AT522904300234201001",
"bal" : [
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLAV"
}
},
"amt" : {
"value" : "72579.83178142391261644661426544189453125",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "72579.83178142391261644661426544189453125",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLAV"
}
},
"amt" : {
"value" : "64433.471462276895181275904178619384765625",
"ccy" : "GBP"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "64433.471462276895181275904178619384765625",
"ccy" : "GBP"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
}
]
}我想做的是对余额类型"CLBD“和货币"EUR”进行排序测试集合。
我尝试了以下几种方法：
db.test.aggregate([
{$unwind: "$bal"}
,{$match: {"bal.amt.ccy": "EUR", "bal.tp.cdOrPrtry.cd":"CLBD"}}
,{$sort: {"bal.amt.value":-1}}
]);输出在某种程度上是好的，即它对数据进行了排序，但它从原始文档中删除了某些字段，即英镑余额、CLAV余额等：
/* 1 */
{
"_id" : "100001-AT522904300234201001",
"agref" : "100001",
"acref" : "AT522904300234201001",
"bal" : {
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "72579.83178142391261644661426544189453125",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
}
}
/* 2 */
{
"_id" : "100001-AT611904300234101001",
"agref" : "100001",
"acref" : "AT611904300234101001",
"bal" : {
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "6452",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
}
}敬请指教。
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-08-10 23:02:47
您可以使用对原始文档的$$ROOT引用，但它会稍微改变结果的结构：
db.test.aggregate([
{
$project: {
bal: { $filter: {
input: "$bal",
as: "bal",
cond: { $and: [
{ $eq: [ "$$bal.amt.ccy", "EUR" ] },
{ $eq: [ "$$bal.tp.cdOrPrtry.cd", "CLBD" ] }
] }
} },
doc: "$$ROOT"
}
},
{
$unwind: "$bal"
},
{
$sort: { "bal.amt.value": -1 }
}
]);将得到一个有序的元素列表，包括包含整个文档的doc字段：
/* 1 */
{
"_id" : "100001-AT522904300234201001",
"bal" : {
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "72579.83178142391261644661426544189453125",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
"doc" : {
"_id" : "100001-AT522904300234201001",
"agref" : "100001",
"acref" : "AT522904300234201001",
"bal" : [
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLAV"
}
},
"amt" : {
"value" : "72579.83178142391261644661426544189453125",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "72579.83178142391261644661426544189453125",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLAV"
}
},
"amt" : {
"value" : "64433.471462276895181275904178619384765625",
"ccy" : "GBP"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "64433.471462276895181275904178619384765625",
"ccy" : "GBP"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
}
]
}
}
/* 2 */
{
"_id" : "100001-AT611904300234101001",
"bal" : {
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "6452",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
"doc" : {
"_id" : "100001-AT611904300234101001",
"agref" : "100001",
"acref" : "AT611904300234101001",
"bal" : [
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLAV"
}
},
"amt" : {
"value" : "6452",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "6452",
"ccy" : "EUR"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLAV"
}
},
"amt" : {
"value" : "5727.8275199999998221755959093570709228515625",
"ccy" : "GBP"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
},
{
"tp" : {
"cdOrPrtry" : {
"cd" : "CLBD"
}
},
"amt" : {
"value" : "5727.8275199999998221755959093570709228515625",
"ccy" : "GBP"
},
"cdtDbtInd" : "CRDT",
"dt" : {
"dt" : "2016-06-30"
}
}
]
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38873758
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号