CODEWITHSUNDEEP
mongodb-query
fpena06
fpena06

Reputation: 2426

Need to get the following query to output correctly

Hi guys I've been trying all day to construct this simple mongo query but I can't get the desire output. Below is the query and the current output.

db.customers.aggregate(
{ $match : { "status" : "Closed" } },
{ $unwind: "$lines" },
{ $group : {
        _id:{label: "$lines.label",date: {$substr: [ "$date", 0, 10 ]}},
        values: { $push:  { date: {$substr: [ "$date", 0, 10 ]}} },
        count: { $sum: 1 }
    }},
{ $project : {
        _id : 1,
        values:1,
        count:1
    }}
);

Which outputs the following.

{
    "result": [
        {
            "_id": {
                "label": "label",
                "date": "2010-10-01"
            },
            "values": [
                {
                    "date": "2010-10-01"
                },
                {
                    "date": "2010-10-01"
                },
                {
                    "date": "2010-10-01"
                },
                {
                    "date": "2010-10-01"
                }
            ],
            "count": 4
        },
        {
            "_id": {
                "label": "label",
                "date": "2010-10-10"
            },
            "values": [
                {
                    "date": "2010-10-10"
                }
            ],
            "count": 1
        },
        {
            "_id": {
                "label": "label",
                "date": "2010-07-25"
            },
            "values": [
                {
                    "date": "2010-07-25"
                }
            ],
            "count": 1
        }
    ]
}

However the output below is the one that I'm looking for and just can't get. I can obviously get all the data I desire, just in the wrong places.

{
    "result": [
        {
            "_id": "label",
            "values": [
                {
                    "date": "2010-11-27",
                    "count": 4
                },
                {
                    "date": "2010-10-10",
                    "count": 1
                },
                {
                    "date": "2010-07-25",
                    "count": 1
                }
            ]
        }
    ]
}

Like always thanks for the help and support.

Upvotes: 1

Views: 29

Answers (1)

d0x
d0x

Reputation: 11571

Can you try this:

db.customers.aggregate([
{ $match : { "status" : "Closed" } },
{ $unwind: "$lines" },
{ $group : {
        _id:{label: "$lines.label",date: {$substr: [ "$date", 0, 10 ]}},
        values: { $push:  { date: {$substr: [ "$date", 0, 10 ]}} },
        count: { $sum: 1 }
    }},
// This one I added to group them behind a single label in an array list
{ $group : {
        _id:{
                label: "$_id.label"
        },
        values : { 
               $push : { date : "$date", count : "$count" } 
        }
  }
},
{ $project : {
        _id : 1,
        values:1,
        count:1
  }
}
]);

If I got your problem right, you like to group the counts + dates in an values array. That you can do with $push after the 1st group stage.

Upvotes: 1

Related Questions