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"
}
]
[
{
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"
}
]
give me top 1 of each TYPE in each LOCATION sorted by ID descending
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:
ID
descending.GROUP BY
clause. In SQL, you can't use GROUP BY
unless 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 the type
, location
and id
keys, and use the required $first
operator to bring in the first document (in other words, the TOP
document when ordered).SELECT
in 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.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);
});