CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
itcoder
itcoder

Reputation: 327

Sort the array in the document with MongoDB

I have data structure from mongoDb v 3.2 I will try make sorting date by month and sorting within statistic array through aggregation-framework

Source data:

   "monthStart" : "2015-11",
"monthEnd" : "2015-11",
"date" : ISODate("2016-03-09T09:06:58Z"),
"statistic" : [
    {
        "name" : "site1.com",
        "ga" : "99999",
        "data" : {
            "desktop" : {
                "users" : NumberLong(25),
                "pageviews" : NumberLong(56789),
            }
        }
    },
    {
        "name" : "site2.com",
        "ga" : "102",
        "data" : {
            "desktop" : {
                "users" : NumberLong(21),
                "pageviews" : NumberLong(9399393),
            }
        }
    },
    {
        "name" : "site3.com",
        "ga" : "103",
        "data" : {
            "desktop" : {
                "users" : NumberLong(103),
                "pageviews" : NumberLong(9399393),
            }
        }
    },
{
"monthStart" : "2015-12",
"monthEnd" : "2015-12",
"date" : ISODate("2016-03-09T09:08:39Z"),
"statistic" : [
    {
        "name" : "site1.com",
        "ga" : "9999",
        "data" : {
            "desktop" : {
                "users" : NumberLong(88),
                "pageviews" : NumberLong(83838),
            },
        }
    },
    {
        "name" : "site2.com",
        "ga" : "8888",
        "data" : {
            "desktop" : {
                "users" : NumberLong(75),
                "pageviews" : NumberLong(77777777),
            },

        }
    },
    {
        "name" : "site3.com",
        "ga" : "103",
        "data" : {
            "desktop" : {
                "users" : NumberLong(25),
                "pageviews" : NumberLong(9399393),
            }
        }
    },
    }
]

How I can get structure sorting within array statistic?

Expected result :

    "monthStart" : "2015-11",
"monthEnd" : "2015-11",
"date" : ISODate("2016-03-09T09:06:58Z"),
"statistic" : [
    {
        "name" : "site3.com",
        "ga" : "103",
        "data" : {
            "desktop" : {
                "users" : NumberLong(103),
                "pageviews" : NumberLong(9399393),
            }
        }
    },
            {
        "name" : "site2.com",
        "ga" : "102",
        "data" : {
            "desktop" : {
                "users" : NumberLong(21),
                "pageviews" : NumberLong(9399393),
            }
        }
    },
    {
        "name" : "site1.com",
        "ga" : "99999",
        "data" : {
            "desktop" : {
                "users" : NumberLong(25),
                "pageviews" : NumberLong(56789),
            }
        }
    },

{
"monthStart" : "2015-12",
"monthEnd" : "2015-12",
"date" : ISODate("2016-03-09T09:08:39Z"),
"statistic" : [
        {
        "name" : "site3.com",
        "ga" : "103",
        "data" : {
            "desktop" : {
                "users" : NumberLong(201),
                "pageviews" : NumberLong(9399393),
            }
        }
    },
    {
        "name" : "site1.com",
        "ga" : "9999",
        "data" : {
            "desktop" : {
                "users" : NumberLong(88),
                "pageviews" : NumberLong(83838),
            },
        }
    },
    {
        "name" : "site2.com",
        "ga" : "8888",
        "data" : {
            "desktop" : {
                "users" : NumberLong(75),
                "pageviews" : NumberLong(77777777),
            },

        }
    },

    }
]

I tried make, but it is didn't work:

     db.TrafficStatistic.aggregate([
{ "$unwind": "$statistic" },
{ "$sort": { "statistic.data.desktop.users": 1 } } ])

Upvotes: 1

Views: 2768

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50406

I see the problem. People are searching for and finding this stackoverflow answer:

how to sort array inside collection record in mongoDB

It's wrong, since it never "reconstructs" the array.

You do that with $group and $push, and since you are grouping you will want $first for the other fields in the document you want:

db.TrafficStatistic.aggregate([
   { "$unwind": "$statistic" },
   { "$sort": { "_id": 1, "statistic.data.desktop.users": 1 } },
   { "$group": {
       "_id": "$_id",
       "monthStart" : { "$first": "$monthStart" },
       "monthEnd" : { "$first": "$monthEnd" },
       "date" : { "$first": "$date" },
       "statistic": { "$push": "$statistic" }
   }}
])

Note also the $sort is applied to both the "_id" and the other field to sort. This is so the sorting is applied per document and is important when the document details are put back together in $group.

Now the document looks the same as it did, but this time the array members are sorted.

Upvotes: 4

Related Questions