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 :)

what `user_count` contain? – IftekharDani Oct 23 '18 at 13:16 — IftekharDani, Oct 23 '18 at 13:16

score 1 · Accepted Answer · answered Oct 23 '18 at 15:44

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

answered Oct 23 '18 at 15:44

Radosław Miernik

4,004
8
33
36

In addition if you can help me out to figure out the way to add multiple filter like if **city_id** is passed than i have to match with that, if **state_id** is passed than i have to match **state_id**, if date range passed than i have to check with in given date range. I am using **jenssegers/laravel-mongodb**. – Prafful Panwar Oct 24 '18 at 06:41
I'm pretty sure you can do it independently from your package. Simply build your query step by step (JS) `var match = {}; if (city_id) match.city_id = city_id; /* ... */`. – Radosław Miernik Oct 24 '18 at 11:35
i tried this in php but in the last its giving error "MongoDB\Driver\Exception\CommandException: the match filter must be an expression in an object in " `if (request()->input('city_id')) { $matchCon = [ "city_id" => request()->input('city_id'), ]; } else { $matchCon = [ "city_id_fake_field" => request()->input('city_id'), ]; } $object = json_decode(json_encode($matchCon), FALSE);` than i was passing `$object` in `$match` condition – Prafful Panwar Oct 24 '18 at 11:50
I'm not familiar with this tooling at all, sorry. – Radosław Miernik Oct 24 '18 at 18:27

MongoDB aggregate + $match + $group + Array

1 Answers1