Moazzam
Reputation: 429
Sort in embedded collection in MongoDB
I am using MongoDb 3.2.8. Below is my test collection containing 2 documents which stores balance information for a bank account. Two balances (closing and available balance are stored against 2 currencies GBP and EUR against each account.
/* 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"
}
}
]
}
What I want to do is sort test collection on balance type "CLBD" and Currency "EUR".
I have tried following:
db.test.aggregate([
{$unwind: "$bal"}
,{$match: {"bal.amt.ccy": "EUR", "bal.tp.cdOrPrtry.cd":"CLBD"}}
,{$sort: {"bal.amt.value":-1}}
]);
The output is fine to some extent i.e. it sorts the data but it removes certain fields from original document i.e. Balances in GBP, CLAV balances etc:
/* 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"
}
}
}
Please advice.
Upvotes: 0
Views: 48
Answers (1)
Alex Blex
Reputation: 37108
You can use $$ROOT reference to the original document, but it will slightly change structure of the result:
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 }
}
]);
will result with an ordered list of elements, including doc field, which contains the whole document:
/* 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"
}
}
]
}
}
Upvotes: 1
Related Questions
- How to sort mongo collection on the basis array/ object type field
- MongoDB sorting
- How to perform this sort in MongoDB? (embedded sorting)
- Advanced mongodb collection sorting
- sort by embedded object value in Mongodb
- how to sort before querying in the embedded document
- MongoDB Sort collection by nested objects fields
- How do I sort a MongoDB collection by a field in an embedded document using Mongoid?
- MongoDB sort by field in embedded that matches query
- Sort a collections base on record of embedded object