CODEWITHSUNDEEP
mongodbmongodb-query
Ritesh Sinha
Ritesh Sinha

Reputation: 840

mongoDB Query-eliminate repeating results

I have a collection which looks like this :

{
"_id" : ObjectId("558c108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : "2015",
"month" : "06",
"day" : "01",
"hour" : "17",
"dayofyear" : "184",
"weekofyear" : 26,
"productcount" : 2,
"count" : 1
}


{
"_id" : ObjectId("558c108b209c022c947b0056"),
"term" : "aero storm tour",
"year" : "2015",
"month" : "06",
"day" : "01",
"hour" : "17",
"dayofyear" : "184",
"weekofyear" : 26,
"productcount" : 2,
"count" : 2
}


{
"_id" : ObjectId("558c108b209c022c947b0026"),
"term" : "aero",
"year" : "2015",
"month" : "06",
"day" : "01",
"hour" : "17",
"dayofyear" : "184",
"weekofyear" : 26,
"productcount" : 23,
"count" : 2
}


{
"_id" : ObjectId("558c108b209c022c947b0022"),
"term" : "aero",
"year" : "2015",
"month" : "06",
"day" : "01",
"hour" : "17",
"dayofyear" : "184",
"weekofyear" : 26,
"productcount" : 23,
"count" : 12
}


{
"_id" : ObjectId("558c108b209c022c947b0032"),
"term" : "aero",
"year" : "2015",
"month" : "07",
"day" : "01",
"hour" : "17",
"dayofyear" : "184",
"weekofyear" : 26,
"productcount" : 23,
"count" : 12
}


{
"_id" : ObjectId("5348c108b09c022c947b0055"),
"term" : "aero storm tour",
"year" : "2015",
"month" : "06",
"day" : "01",
"hour" : "17",
"dayofyear" : "170",
"weekofyear" : 26,
"productcount" : 235,
"count" : 1
}


{
"_id" : ObjectId("658c108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : "2015",
"month" : "06",
"day" : "01",
"hour" : "17",
"dayofyear" : "173",
"weekofyear" : 26,
"productcount" : 235,
"count" : 1
}


{
"_id" : ObjectId("558c108b209c022c947a0055"),
"term" : "aero storm tour",
"year" : "2015",
"month" : "06",
"day" : "01",
"hour" : "17",
"dayofyear" : "164",
"weekofyear" : 26,
"productcount" : 235,
"count" : 1
}

I need to find the term where productcount is less than equals to 2 on a particular year , month and day .

My query :

db.tq.find( 
            { year : "2015" , month : "06" , day : "01" ,productcount: { $lte: 2 } } ,
            { _id: 0, term: 1, productcount: 1 }

          )

Output :

{
"term" : "aero storm tour",
"productcount" : 2
}
{
"term" : "aero storm tour",
"productcount" : 2
}

It returns the correct result but I want to avoid repetitive results by using something like distinct or similar.

Expected output:

{
"term" : "aero storm tour",
"productcount" : 2
}

How to achieve that ?

Upvotes: 0

Views: 192

Answers (1)

Vishwas
Vishwas

Reputation: 7067

Your find query is not working because for the date you are matching, there are two documents in your collection. That is why it is giving you two documents as a result.

You can work add limit to find query to get only one result like following-

db.collection.find({
    year: "2015",
    month: "06",
    day: "01",
    productcount: {
        $lte: 2
    }
    }, {
    _id: 0,
    term: 1,
    productcount: 1
    }
).limit(1)

Otherwise

You can use mongo aggregation to get expected result as shown as following:

db.collection.aggregate({
    $match: {
    productcount: {
        $lte: 2
    },
    year: "2015",
    month: "06",
    day: "01"
    }
}, {
    $group: {
    _id: "$term",
    "term": {
        $first: "$term"
    },
    "productcount": {
        $first: "$productcount" //u can add $max:"$productcount" to get productcount with max value instead of $first: "$productcount"
    }
    }
}, {
    $project: {
    "term": 1,
    "productcount": 1,
    "_id": 0
    }
})

Upvotes: 2

Related Questions