Robmongo - combine unique values based on different columns

Question

Robmongo - combine unique values based on different columns

As a newcomer to robmongo, I've been tasked with writing queries for a collection that includes keys like "userId" and "deviceModel." My goal is to create a query that shows the number of users for each device model.

Here is the query I have so far:

db.device_data.aggregate([{"$group": {"_id": "$data.deviceModel", "count": {"$sum": 1}}}])

However, this query currently only counts the number of keys for each device, not the number of users.

{
   "_id": { "$binary": "AN6GmE7Thi+Sd/dpLRjIilgsV/4AAAg=", "$type": "00" },
   "auditVersion": "1.0",
   "currentTime": NumberLong(1479301118381),
   "data": {
    "deviceDesign": "bullhead",
    "loginType": "GOOGLE",
    ...

This is an example of what one key looks like in the collection.

javascript mongodb aggregation-framework

Answer 1

Answer №1

If you want to find the distinct count of userId for each deviceModel, you can use the following query. This query will only count a userId once if it appears multiple times for the same deviceModel.

db.collection.aggregate([
    {"$group" : {_id:"$data.deviceModel", userIds:{$addToSet: "$userId"}}},
    {
        $unwind:"$userIds"
    },
    {
        $group: { _id: "$_id", userIdCount: { $sum:1} }
    }
])

Unwind:-

The $unwind operator deconstructs an array field from the input documents to output a document for each element.

In the above query, the $unwind operator deconstructs the userId array created in the first pipeline.

addToSet:-

The $addToSet operator returns an array of all unique values resulting from applying an expression to each document in a group of documents sharing the same group by key.

This ensures that only unique values are added to an array. In this case, the userId is added to an array in the first pipeline.

Answer 2

If you want to find the distinct count of userId for each deviceModel, you can use the following query. This query will only count a userId once if it appears multiple times for the same deviceModel.

db.collection.aggregate([
    {"$group" : {_id:"$data.deviceModel", userIds:{$addToSet: "$userId"}}},
    {
        $unwind:"$userIds"
    },
    {
        $group: { _id: "$_id", userIdCount: { $sum:1} }
    }
])

Unwind:-

The $unwind operator deconstructs an array field from the input documents to output a document for each element.

In the above query, the $unwind operator deconstructs the userId array created in the first pipeline.

addToSet:-

The $addToSet operator returns an array of all unique values resulting from applying an expression to each document in a group of documents sharing the same group by key.

This ensures that only unique values are added to an array. In this case, the userId is added to an array in the first pipeline.

Robmongo - combine unique values based on different columns

Answer №1

Similar questions

Organize AngularJS ng-repeat using dictionary information

md- The datePicker component consistently encounters errors with Date instances

Inline checkbox label with Bootstrap 5 styling

Comparing the differences between while loops and setTimeout function in JavaScript

Preventing Flash of Unstyled Content in ElectronJS Browser Windows

How can you use jQuery to remove a class from an element after a specified period of time?

Searching for users with specific patterns of string using LDAP JS in Node JS

Using TypeScript with ReactJS

Color scheme for navigation bar carousel item background color

Live AJAX inquiries in progress

The value of the checked input with the name `nameofinput` is currently not defined

The authService is facing dependency resolution issues with the jwtService, causing a roadblock in the application's functionality

What is the best way to create a taskbar using HTML or Javascript?

Utilize JavaScript to load external JavaScript libraries and scripts

Passing data between Vue.js components effortlessly without relying on events

Retrieve the audio file from the server endpoint and stream it within the Vue application

Using jQuery to extract a specific portion of a URL path

What is the most efficient way to retrieve key pair values of separate elements in an array with just one API request?

Is there a way to connect two tables using CSS pseudo-selectors?

When invoked, a Javascript Object comes back empty