CODEWITHSUNDEEP
mongodb
Aman Maurya
Aman Maurya

Reputation: 1325

How to query in mongodb to get distinct record with count

I have collection who's name is transactions. I'm sharing the object of transactions collection

{
    "_id" : ObjectId("58aaec83f1dc6914082afe31"),
    "amount" : "33.00",
    "coordinates" : {
        "lat" : "4.8168",
        "lon" : "36.4909"
    },
    "cuisine" : "Mexican",
    "date" : ISODate("0062-02-22T11:46:52.738+05:30"),
    "location" : {
        "address" : "2414 Trudie Rue",
        "city" : "West Alisa",
        "state" : "New York",
        "zip" : "10000"
    },
    "place_name" : "Outdoors",
    "place_type" : "Wooden"
},
{
    "_id" : ObjectId("58aaec83f1dc6914082afe32"),
    "amount" : "557.00",
    "coordinates" : {
        "lat" : "-36.6784",
        "lon" : "131.3698"
    },
    "cuisine" : "Australian",
    "date" : ISODate("1294-10-04T19:53:15.562+05:30"),
    "location" : {
        "address" : "5084 Buckridge Cove",
        "city" : "Sylviaview",
        "state" : "Hawaii",
        "zip" : "51416-6918"
    },
    "place_name" : "Toys",
    "place_type" : "Cotton"
},
{
    "_id" : ObjectId("58aaec83f1dc6914082afe33"),
    "amount" : "339.00",
    "coordinates" : {
        "lat" : "45.1468",
        "lon" : "91.4097"
    },
    "cuisine" : "Mexican",
    "date" : ISODate("1568-11-25T02:54:53.046+05:30"),
    "location" : {
        "address" : "94614 Harry Island",
        "city" : "Cartwrightside",
        "state" : "Louisiana",
        "zip" : "18825"
    },
    "place_name" : "Clothing",
    "place_type" : "Frozen"
},
{
    "_id" : ObjectId("58aaec83f1dc6914082afe34"),
    "amount" : "173.00",
    "coordinates" : {
        "lat" : "-57.2738",
        "lon" : "19.6381"
    },
    "cuisine" : "Australian",
    "date" : ISODate("0804-05-07T03:00:07.724+05:30"),
    "location" : {
        "address" : "1933 Lewis Street",
        "city" : "Aufderharville",
        "state" : "Louisiana",
        "zip" : "23416"
    },
    "place_name" : "Beauty",
    "place_type" : "Fresh"
},
{
    "_id" : ObjectId("58aaec83f1dc6914082afe34"),
    "amount" : "173.00",
    "coordinates" : {
        "lat" : "-57.2738",
        "lon" : "19.6381"
    },
    "cuisine" : "Australian",
    "date" : ISODate("0804-05-07T03:00:07.724+05:30"),
    "location" : {
        "address" : "1933 Lewis Street",
        "city" : "Aufderharville",
        "state" : "Louisiana",
        "zip" : "23416"
    },
    "place_name" : "Beauty",
    "place_type" : "Fresh"
}

I want to get the list of distinct cuisine with total count

Output

{
   "name" : 'Mexican',
   "count" : '2'
},
{
   "name" : 'Australian',
   "count" : '3'
},

I could have done easily with mysql but I dot know in mongodb as I'm new with mongodb

I have tried with the example and I found nothing:

db.transactions.aggregate(
    {$group: {_id:'$cuisine'},count:{$sum:1}}
    ).result;

Upvotes: 2

Views: 3866

Answers (1)

Hasan Alper Ocalan
Hasan Alper Ocalan

Reputation: 318

Please try the code below. You should group by cuisine the records and get the count of them. Later in project pipeline you can define the final look.

db.transactions.aggregate([
{ $group: { _id: "$cuisine", count: { $sum: 1 } } }, 
{ $project:{ _id: 0, name: "$_id", count:"$count" } }
]);

Upvotes: 2

Related Questions