How to query in mongodb to get distinct record with count

Question

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;

Possible duplicate of [MongoDB select count(distinct x) on an indexed column - count unique results for large data sets](http://stackoverflow.com/questions/11782566/mongodb-select-countdistinct-x-on-an-indexed-column-count-unique-results-for) — hassan, Feb 21 '17 at 08:10
thank @HassanAhmed for the comment but i didn't found that post very helpful — Aman Maurya, Feb 21 '17 at 08:19

score 2 · Answer 1 · answered Feb 21 '17 at 08:19

2

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" } }
]);

answered Feb 21 '17 at 08:19

Hasan Alper Ocalan

308
1
9

Oh thanks you @hasan this is what i was looking ,but can you explain me how it's working and what I was doing wrong? – Aman Maurya Feb 21 '17 at 08:23
First of all aggregation framework is working with pipeline stages, so aggregate function takes an array as parameter. In this array you should define the pipeline stages. In your question first stage has to be a group function and you did it well. In the second stage you have to use project function which will reshape the result of the query. For example first group stage will return something like that : { "_id" : 'Mexican', "count" : '2' }, { "_id" : 'Australian', "count" : '3' }, Project stage is reshaping it like you get the result. – Hasan Alper Ocalan Feb 21 '17 at 08:28

How to query in mongodb to get distinct record with count

1 Answers1