Node.js Question
Return records in mongoDB based on top 1 of field x grouped by type y
If i have the following json structure:
[
{
id: 1,
type: "Drinks",
isActive : "true",
location: "QLD"
},
{
id: 2,
type: "Drinks",
isActive : "false",
location: "NSW"
},
{
id: 3,
type: "Drinks",
isActive : "true"
location: "QLD"
},
{
id: 3,
type: "Drinks",
isActive : "false"
location: "QLD"
},
{
id: 3,
type: "Drinks",
isActive : "true"
location: "QLD"
},
{
id: 4,
type: "Food",
isActive : "true"
location: "NSW"
},
{
id: 4,
type: "Food",
isActive : "false"
location: "NSW"
}
]
The return i'm interested in is:
[
{
id: 1,
type: "Drinks",
isActive : "true",
location: "QLD"
},
{
id: 2,
type: "Drinks",
isActive : "false",
location: "NSW"
},
{
id: 3,
type: "Drinks",
isActive : "true",
location: "QLD"
},
{
id: 4,
type: "Food",
isActive : "false",
location: "NSW"
}
]
In other words,
give me top 1 of each TYPE in each LOCATION sorted by ID descendingIt many not be relevant but i am using Mongoose within my nodejs app to interact with mongoDB.
Answer Source
The aggregation framework is at your disposal to give you the desired results. You would have to run an aggregation pipeline consists of 3 stages, in the following order:
-
- This pipe will allow you to first order the documents getting into the pipeline for grouping later. Sort the documents with the
IDdescending.
- This pipe will allow you to first order the documents getting into the pipeline for grouping later. Sort the documents with the
-
- The group pipeline operator is similar to the SQL's
GROUP BYclause. In SQL, you can't useGROUP BYunless you use any of the aggregation functions. The same way, you have to use an aggregation function in MongoDB as well. In this instance, you need to group all the documents by thetype,locationandidkeys, and use the required$firstoperator to bring in the first document (in other words, theTOPdocument when ordered).
- The group pipeline operator is similar to the SQL's
-
- This pipeline step is similar to
SELECTin SQL. Use this to rename the field names and select/deselect the fields to be returned, out of the grouped fields. If you specify 0 for a field, it will NOT be sent in the pipeline to the next operator.
- This pipeline step is similar to
Putting all the three pipes together, you can run the following aggregation pipeline to achieve the required result:
var pipeline = [
{ "$sort": { "id": -1 } },
{
"$group": {
"_id": {
"type": "$type", "location": "$location", "id": "$id"
},
"isActive": { "$first": "$isActive" }
}
},
{
"$project": {
"_id": 0,
"id": "$_id.id",
"isActive": 1,
"type": "$_id.type",
"location": "$_id.location"
}
}
]
Model.aggregate(pipeline, function(err, result) {
if err throw err;
console.log(result);
});
Or using the fluent API
Model.aggregate()
.sort("-id")
.group({
"_id": {
"type": "$type", "location": "$location", "id": "$id"
},
"isActive": { "$first": "$isActive" }
})
.project({
"_id": 0,
"id": "$_id.id",
"isActive": 1,
"type": "$_id.type",
"location": "$_id.location"
})
.exec(unction(err, result) {
if err throw err;
console.log(result);
});