MongoDB equivalent of an SQL query to get the distinct values of a field in a collection including the count of documents which have each distinct value (distinct with count)

100%

MongoDB equivalent of an SQL query to get the distinct values of a field in a collection including the count of documents which have each distinct value (distinct with count)

✓ Published0🌍 Public

Last edited Aug 10, 2015

Created on May 23, 2016

This example demonstrates how to retrieve distinct values of a field in a MongoDB collection along with the document count for each value, mirroring a SQL `GROUP BY` query. It shows two aggregation pipelines using MongoDB’s `aggregate()` method with the `$group` stage to group by the field `_id` and compute a `count` via `$sum:1`, and an optional `$sort` stage to order the results by count descending. The first pipeline returns unsorted counts, while the second sorts them from highest to lowest. No external visualization library is used; the output is raw aggregated data from the database.

AI-generated description

Similar vizzes

Loading thumbnail…

MongoDB Aggregation Framework way for "select count (distinct fieldName) from someTable" for large collections

This example shows how to compute the equivalent of a SQL “select count (distinct fieldName) from someTable” using MongoDB’s Aggregation Framework for large collections. The code uses the `db.someCollection.aggregate()` method with two `$group` stages: the first groups documents by the distinct value of `fieldName` (assigned to `_id`), and the second groups all results into a single document (`_id: 1`) while summing the number of groups with `$sum: 1` to produce the final count. The snippet is written as a JavaScript gist and relies solely on MongoDB’s native aggregation pipeline, with no external visualization library.

Aackuser

81% match

Loading thumbnail…

[统计mongodb 中某个 key 对应的 value 的出现频数] 统计一个 mongodb 数据库的 collection 中，所有 documents 内，统计某个特定字段的不同值各自出现次数。 #mongodb #pymongo

This example shows how to count the frequency of distinct values for a specified field across all documents in a MongoDB collection. The code defines a Python function using PyMongo’s `aggregate` method with MongoDB aggregation pipelines. It handles both simple fields and fields containing arrays of subdocuments, where a subfield is specified. The function filters documents with `$match`, unwinds arrays with `$unwind`, groups by value with `$group`, and sorts results by count using `$sort`.

SShawn91

72% match

Loading thumbnail…

Aggregate data from MongoDB with Node.js and mongoose

This example demonstrates how to calculate an account balance by aggregating embedded financial records in MongoDB using Node.js and Mongoose. It defines Account and Record schemas, inserts sample data, and runs an aggregation pipeline with `$match`, `$unwind`, and `$group` to sum record amounts via `AccountModel.aggregate()`. The code also shows CRUD operations for accounts and records, with results logged to the console.

Aackuser

58% match

Loading thumbnail…

Gist 737033

This example demonstrates a MapReduce aggregation over a MongoDB collection of crime records, showing the count of crimes grouped by property name. The code first seeds a `crimes` collection with sample data, then defines a `map` function that emits each property name with a value of 1, and a `reduce` function that sums the emitted values. The MapReduce operation runs via `db.crimes.mapReduce()` and outputs the results as a JSON array using `printjson`. It uses MongoDB’s JavaScript shell API for data insertion, mapping, reduction, and result retrieval.

CCaged

56% match

Loading thumbnail…

Count disctinct values for each columns of a file with awk

This example uses an awk script to count distinct values in every column of a CSV file, writing each column’s unique entries and their occurrence counts to a separate output CSV named after the original file and column header. The script first extracts the header row to create per-column output files, then iterates over all subsequent rows, tallying values in a two-dimensional associative array keyed by field name and value. In the END block, it splits those keys to write the final counts. The awk code relies on built-in features like the `FS` field separator, the `split` function, and the `SUBSEP` separator for array keys.

Aant1j

55% match

Loading thumbnail…

GROQ - tricks

This example demonstrates a collection of GROQ query patterns for retrieving and aggregating data from a Sanity CMS dataset. The code shows how to filter and sort project milestones to find the latest status, use boolean checks to determine project completion, implement pagination for posts, and perform SQL-like grouping by counting related documents. It introduces GROQ's native aggregation functions such as `count()`, `math::sum`, `math::avg`, and `math::max` to compute statistics directly within the query language. The group-by example uses a parent-centric projection with the `references(^._id)` function to achieve relational grouping.

Aabonello

53% match

Loading thumbnail…

Mongo Basics Drills

This example shows a series of MongoDB shell commands that demonstrate basic CRUD operations and query patterns against a restaurant dataset. The code performs actions like finding all documents, sorting and limiting results, retrieving documents by `_id` or field values, counting records, deleting single documents, and updating either one or many documents using `updateOne` and `update` with the `$set` operator. It also illustrates how to obtain an `ObjectId` from a query result and use it for subsequent operations. The visualization presents these commands as a linear drill sequence, making the MongoDB query syntax and workflow explicit.

Aarcox80

53% match

Loading thumbnail…

MapReduce with MongoDB in NodeJS

This example demonstrates a MapReduce operation in MongoDB, executed from a Node.js application, that aggregates sensor measurements into hourly averages per user and device. The code defines custom `map`, `reduce`, and `finalize` functions to group raw data points by a composite key of user email, device UID, and the hour timestamp, then computes a weighted average value and total weight. The operation queries MongoDB’s `Measure` collection, merges results into an output collection named `measure1hs`, and reports execution statistics via a callback. The implementation uses MongoDB’s native `db.Measure.collection.mapReduce` API directly, bypassing Mongoose, and handles asynchronous completion through a callback function.

Aackuser

48% match