MongoDB aggregate + $match + $group + Array

Question

Here is my MongoDB query :

profiles.aggregate([{"$match":{"channels.sign_up":true}},{"$group":{"_id":"$channels.slug","user_count":{"$sum":1}}},{"$sort":{"user_count":-1}}])

Here is my Code :

$profiles = Profile::raw()->aggregate([
            [
                '$match' => [
                    'channels.sign_up' => true
                ]
            ],
            [
                '$group' => [
                    '_id' => '$channels.slug',
                    'user_count' => ['$sum' => 1]
                ]
            ],
            [
                '$sort' => [
                    "user_count" => -1
                ]
            ]
        ]);

Here is my Mongo Collection :

"channels": [
        {
            "id": "5ae44c1c2b807b3d1c0038e5",
            "slug": "swachhata-citizen-android",
            "mac_address": "A3:72:5E:DC:0E:D1",
            "sign_up": true,
            "settings": {
                "email_notifications_preferred": true,
                "sms_notifications_preferred": true,
                "push_notifications_preferred": true
            },
            "device_token": "ff949faeca60b0f0ff949faeca60b0f0"
        },
        {
            "id": "5ae44c1c2b807b3d1c0038f3",
            "slug": "website",
            "mac_address": null,
            "device_token": null,
            "created_at": "2018-06-19 19:15:13",
            "last_login_at": "2018-06-19 19:15:13",
            "last_login_ip": "127.0.0.1",
            "last_login_user_agent": "PostmanRuntime/7.1.5"
        }
],

Here is my response :

   {
        "data": [
            {
                "_id": [
                    "swachhata-citizen-android"
                ],
                "user_count": 1
            },
            {
                "_id": [
                    "icmyc-portal"
                ],
                "user_count": 1
            },
            {
                "_id": [
                    "swachhata-citizen-android",
                    "website",
                    "icmyc-portal"
                ],
                "user_count": 1
            }
        ]
    }

what i am expecting is :

{
    "data": [
        {
            "_id": [
                "swachhata-citizen-android"
            ],
            "user_count": 1
        },
        {
            "_id": [
                "icmyc-portal"
            ],
            "user_count": 1
        },
        {
            "_id": [
                "website",
            ],
            "user_count": 1
        }
    ]
}

As you can see channels is an array and "sign_up" is true only for one element in array from where user is registered as we have many app so we have to maintain more than 1 channel for users.

i want to data how many user registered with different channels but in response its coming all the channel instead of one channel where sign_up is true.

Also count is wrong as i have to records where "slug": "swachhata-citizen-android" and "sign_up": true.

Need suggestion :)

Answer 1

Use $unwind to transform each document with arrays to array of documents with nested fields. In your example, like this:

profiles.aggregate([
  {$unwind: '$channels'},
  {$match: {'channels.sign_up': true}},
  {$group: {_id: '$channels.slug', user_count: {$sum: 1}}},
  {$sort: {user_count: -1}}
])