0

i have a collection with this documents:

{
        "_id" : BinData(3,"utMu84VFQk+p6LOEE3Ia3w=="),
        "DepartDate" : ISODate("2017-08-14T02:00:00.000+02:00"),
        "Origin" : {
            "Code" : "MIL",
            "City" : "Milano",
            "Country" : "IT"
        },
        "Destination" : {
            "Code" : "TIA",
            "City" : "Tirana",
            "Country" : "AL"
        },
        "ReturnDate" : ISODate("2017-08-21T18:05:00.000+02:00"),
        "SearchDate" : ISODate("2017-07-15T02:00:00.000+02:00"),
        "Duration" : 150,
        "Price" : "376.79",
        "PriceCents" : 37679,
        "Currency" : "EUR",
},
{
        "_id" : BinData(3,"utMu845468+p6Lertya3w=="),
        "DepartDate" : ISODate("2017-08-14T02:00:00.000+02:00"),
        "Origin" : {
            "Code" : "AOI",
            "City" : "Ancona",
            "Country" : "IT"
        },
        "Destination" : {
            "Code" : "TIA",
            "City" : "Tirana",
            "Country" : "AL"
        },
        "ReturnDate" : ISODate("2017-08-21T18:05:00.000+02:00"),
        "SearchDate" : ISODate("2017-07-15T02:00:00.000+02:00"),
        "Duration" : 100,
        "Price" : "376.79",
        "PriceCents" : 37679,
        "Currency" : "EUR",
},
    .........
    .........

I want to group by ONLY Destination.City and return an object with Destination.City, Destination.Code and count. Like this example:

/* 1 */
{
    "_id" : {
        "Code" : "PMO",
        "City" : "Palermo",
        "Country" : "IT"
    },
    "count" : 184564.0
},

/* 2 */
{
    "_id" : {
        "Code" : "CAG",
        "City" : "Cagliari",
        "Country" : "IT"
    },
    "count" : 130873.0
},
    ......
    ......

I tried with this query (i use MongoBooster):

db.parsed_data.aggregate([
    { "$match" : { "Destination.Country" : "IT", "Segments" : { "$elemMatch" : { "Carrier" : "AZ" } } } }, 
    { "$group" : { "_id" : { "City" : "$Destination.City"}, "count" : { "$sum" : 1 } } },
    { "$project" : {"Destination.Code" : 1, "Destination.City" : 1, "Destination.Country" : 1 }},
    { "$sort" : { "count" : -1 } }, 
    { "$limit" : 9 }])

It returns grouped by City but it shows only the City Name. In this way:

/* 1 */
{
    "_id" : {
        "City" : "Lampedusa"
    }
},

/* 2 */
{
    "_id" : {
        "City" : "Trieste"
    }
},

This is not correct. How can i group by ONLY the Destination.City and return an object with Destination.City, Destination.Code and count?

Thx

  • Change your `$group` to include the other fields instead of `$project` stage. Something like `{ "$group" : { "_id" : { "City" : "$Destination.City", other fields here}, "count" : { "$sum" : 1 } } }` or you can use `$first` to include them separately. Something like `{ "$group" : { "_id" : { "City" : "$Destination.City"}, Code:{"$first":"$Destination.City"}, "count" : { "$sum" : 1 } } }` – s7vr Oct 12 '17 at 14:48
  • Possible duplicate of [mongo group query how to keep fields](https://stackoverflow.com/questions/16662405/mongo-group-query-how-to-keep-fields) – s7vr Oct 12 '17 at 14:54
  • This solves my problem: $first to include them separately. Something like { "$group" : { "_id" : { "City" : "$Destination.City"}, Code:{"$first":"$Destination.City"}, "count" : { "$sum" : 1 } } Thanks! –  Nov 20 '17 at 09:15

0 Answers0