Skip to content

Instantly share code, notes, and snippets.

@saintc0d3r

saintc0d3r/01_aggregate_sum.js

Last active April 22, 2024 07:50
Show Gist options
  • Save saintc0d3r/a2029d71d1178eed62f6 to your computer and use it in GitHub Desktop.
Save saintc0d3r/a2029d71d1178eed62f6 to your computer and use it in GitHub Desktop.
Download ZIP
[MongoDb][Javascript-Mongo] Aggregate Queries in MongoDb ( $sum, $avg, $addToSet, $push, $max, $min, double grouping, $match, $project, $sort, $limit, $skip). To run these samples: mongo <sample js file>
Raw
01_aggregate_sum.js
// Let's say we have a store database
db = connect("localhost:27017/store");
var collectionNames = db.getCollectionNames();
for(var i=0; i < collectionNames.length; i++){
if (collectionNames[i] == "products"){
print("[INFO] - Products collection is exists. Dropping it now.");
db.products.drop();
break;
}
}
// And we want to use store to keep our products information
print("[INFO] - Initialise products collection..");
db.products.insert({'manufacturer': 'Intel', 'category':'CPUs-Processors-Desktop', 'name':'Intel Core i7-4960X Ivy Bridge-E 6-Core 3.6GHz (Turbo 4GHz) LGA 2011 130W Desktop Processor BX80633i74960X', 'price': 1049.99 })
db.products.insert({'manufacturer': 'Intel', 'category':'CPUs-Processors-Desktop', 'name':'Intel Core i7-4790 Haswell Quad-Core 3.6GHz LGA 1150 84W Desktop Processor Intel HD Graphics 4600 BX80646I74790', 'price': 314.99 })
db.products.insert({'manufacturer': 'ASUS', 'category':'Intel Motherboards', 'name':'ASUS Sabertooth X79 LGA 2011 Intel X79 SATA 6Gb/s USB 3.0 ATX Intel Motherboard', 'price': 314.99 })
db.products.insert({'manufacturer': 'ASUS', 'category':'Desktop Graphics Cards', 'name':'ASUS GTX770-DC2OC-2GD5 GeForce GTX 770 2GB 256-Bit GDDR5 PCI Express 3.0 HDCP Ready SLI Support Video Card', 'price': 329.99 })
db.products.insert({'manufacturer': 'Corsair', 'category':'Desktop Memory', 'name':'CORSAIR Dominator Platinum 16GB (2 x 8GB) 240-Pin DDR3 SDRAM DDR3 2400 Desktop Memory Model CMD16GX3M2A2400C10', 'price': 274.99 })
db.products.insert({'manufacturer': 'Zotac', 'category':'Desktop Graphics Cards', 'name':'ZOTAC ZT-70301-10P GeForce GTX 770 2GB 256-Bit GDDR5 PCI Express 3.0 Video Card', 'price': 371.90 })
db.products.insert({'manufacturer': 'Gigabyte', 'category':'Desktop Graphics Cards', 'name':'GIGABYTE GV-N770OC-4GD GeForce GTX 770 4GB 256-Bit GDDR5 PCI Express 3.0 HDCP Ready WindForce 3X 450W Video Card', 'price': 369.99 })
db.products.insert({'manufacturer': 'Gigabyte', 'category':'Intel Motherboards', 'name':'GIGABYTE GA-Z97X-Gaming G1 WIFI-BK LGA 1150 Intel Z97 HDMI SATA 6Gb/s USB 3.0 ATX Intel Motherboard', 'price': 359.99 })
// Supposed we want to get a list of products quantity group by manufacturer,
// In SQL, the query would be looked like: select manufacturer, count(*) from products group by manufacturer
// In mongodb, this is how we should do:
print("[INFO] - Number of products, grouped by manufacturer: ")
db.products.aggregate([
{
$group: {
_id: "$manufacturer",
'num_products': {
$sum: 1
}
}
}
]).shellPrint();
// Count number of products by category
print("[INFO] - Number of products, grouped by category: ")
db.products.aggregate([
{
$group: {
_id: "$category",
'num_products': {
$sum: 1
}
}
}
]).shellPrint();
Raw
02_aggregate_compund.js
// Creating foo database
db = connect("localhost:27017/foo");
// Dropping bar if exists
var collectionNames = db.getCollectionNames();
for(var i=0; i < collectionNames.length; i++){
if (collectionNames[i] == "bar"){
print("[INFO] - bar collection is exists. Dropping it now.");
db.bar.drop();
break;
}
}
// Initialising bar's documents
print("[INFO] - Initialise bar collection..");
db.bar.insert({'a': 1, 'b':1, 'c':1});
db.bar.insert({'a': 2, 'b':2, 'c':1});
db.bar.insert({'a': 3, 'b':3, 'c':1});
db.bar.insert({'a': 3, 'b':3, 'c':2});
db.bar.insert({'a': 3, 'b':5, 'c':3});
db.bar.insert({'a': 3, 'b':3, 'c':2});
// We want to aggregate them by a, b then c
print('[INFO] - Quantities by grouped a,b & c altogether.');
db.bar.aggregate([
{
$group: {
_id: {
'Alpha': "$a",
'Bravo': "$b",
'Charlie': "$c"
},
'quantity': {
$sum: 1
}
}
}
]).shellPrint();
Raw
03_compound_ids.js
// Creating foo database
db = connect("localhost:27017/foo");
// Dropping bar if exists
var collectionNames = db.getCollectionNames();
for(var i=0; i < collectionNames.length; i++){
if (collectionNames[i] == "bar"){
print("[INFO] - bar collection is exists. Dropping it now.");
db.bar.drop();
break;
}
}
// A document's id could be made from 2 or more keys.
print("[INFO] - Initialise bar collection..");
db.bar.insert({_id: {'a':1, 'b':1}, 'c':'alpha'})
db.bar.insert({_id: {'a':2, 'b':2}, 'c':'bravo'})
db.bar.insert({_id: {'a':3, 'b':3}, 'c':'charlie'})
db.bar.insert({_id: {'a':4, 'b':4}, 'c':'delta'})
db.bar.insert({_id: {'a':5, 'b':5}, 'c':'echo'})
// Let's try to get a document by invoking this query
db.bar.find({_id: {a:1}}).shellPrint();
// Failed. Try another chance
db.bar.find({_id: {b:1}}).shellPrint();
// Failed. Last chance
print("[INFO] - Find documents where a = 2, b = 2:");
db.bar.find({_id: {a:2, b:2}}).shellPrint();
// Got it. The result should be looked like this:
// { "_id" : { "a" : 2, "b" : 2 }, "c" : "bravo" }
Raw
04_aggregate_sum_states_zips.js
// Creating states database
db = connect("localhost:27017/states");
// Dropping bar if exists
var collectionNames = db.getCollectionNames();
for(var i=0; i < collectionNames.length; i++){
if (collectionNames[i] == "zips"){
print("[INFO] - zips collection is exists. Dropping it now.");
db.bar.drop();
break;
}
}
// Initialise zips collection's documents.
print("[INFO] - Initialise zips collection..");
db.zips.insert({'city': 'Bandung', 'pop': 2575478, 'state': 'ID-JB' })
db.zips.insert({'city': 'Tasikmalaya', 'pop': 635424, 'state': 'ID-JB' })
db.zips.insert({'city': 'Cirebon', 'pop': 2065142, 'state': 'ID-JB' })
db.zips.insert({'city': 'Surabaya', 'pop': 3123914, 'state': 'ID-JI' })
db.zips.insert({'city': 'Malang', 'pop': 820243, 'state': 'ID-JI' })
db.zips.insert({'city': 'Denpasar', 'pop': 788589, 'state': 'ID-BA' })
db.zips.insert({'city': 'Singaraja', 'pop': 80500, 'state': 'ID-BA' })
db.zips.insert({'city': 'Jogja', 'pop': 388627, 'state': 'ID-JT' })
db.zips.insert({'city': 'Surakarta', 'pop': 499337, 'state': 'ID-JT' })
// Now, we want to get aggregated total population by states
print("[INFO] - Get a list of states along with their population");
db.zips.aggregate([
{
$group: {
_id: "$state",
"population": {
$sum: "$pop"
}
}
}
]).shellPrint();
Raw
05_aggregate_avg.js
// Let's say we have a store database where it has products collection as can be seen in 01_aggregate_sum.js
db = connect("localhost:27017/store");
// Then, we want to get a list of average price of these products grouped by category & manufacturer.
print("[INFO] - A list of Products average price, grouped by category: ")
db.products.aggregate([
{
$group: {
_id: "$category",
'average_price': {
$avg: "$price"
}
}
}
]).shellPrint();
print("[INFO] - A list of Products average price, grouped by manufacturer: ")
db.products.aggregate([
{
$group: {
_id: "$manufacturer",
'average_price': {
$avg: "$price"
}
}
}
]).shellPrint();
Raw
06_aggregate_addToSet.js
// Let's say we have a store database where it has products collection as can be seen in 01_aggregate_sum.js
db = connect("localhost:27017/store");
// Then, we want to get a list of Manufacturers along with their product categories
print("[INFO] - A list of product categories, grouped by manufacturer: ")
db.products.aggregate([
{
$group: {
_id: "$manufacturer",
'product_categories': {
$addToSet: "$category"
}
}
}
]).shellPrint();
Raw
07_aggregate_push.js
// Let's say we have a store database where it has products collection as can be seen in 01_aggregate_sum.js
db = connect("localhost:27017/store");
// $push is similar to $addToSet. Except, it lets the duplicated result intact.
print("[INFO] - A list of product categories (DUPLICATED), grouped by manufacturer: ")
db.products.aggregate([
{
$group: {
_id: "$manufacturer",
'product_categories': {
$push: "$category"
}
}
}
]).shellPrint();
Raw
08_aggregate_min_max.js
// Let's say we have a store database where it has products collection as can be seen in 01_aggregate_sum.js
db = connect("localhost:27017/store");
// Then, we want to get a list of Manufacturers along with maximum & minimum price of one of their products
print("[INFO] - A list of maximum products prices, grouped by their manufacturers: ")
db.products.aggregate([
{
$group: {
_id: "$manufacturer",
max_price: {
$max: "$price"
},
min_price: {
$min: "$price"
}
}
}
]).shellPrint();
Raw
09_aggregate_double_grouping.js
// Let's say we have a store database
db = connect("localhost:27017/store");
var collectionNames = db.getCollectionNames();
for(var i=0; i < collectionNames.length; i++){
if (collectionNames[i] == "products"){
print("[INFO] - Products collection is exists. Dropping it now.");
db.products.drop();
break;
}
}
// And we want to use store to keep our products information
print("[INFO] - Initialise products collection..");
db.products.insert({'manufacturer': 'Intel', 'category':'CPUs-Processors-Desktop', 'name':'Intel Core i7-4960X Ivy Bridge-E 6-Core 3.6GHz (Turbo 4GHz) LGA 2011 130W Desktop Processor BX80633i74960X', 'price': 1049.99 })
db.products.insert({'manufacturer': 'Intel', 'category':'CPUs-Processors-Desktop', 'name':'Intel Core i7-4790 Haswell Quad-Core 3.6GHz LGA 1150 84W Desktop Processor Intel HD Graphics 4600 BX80646I74790', 'price': 314.99 })
db.products.insert({'manufacturer': 'ASUS', 'category':'Intel Motherboards', 'name':'ASUS Sabertooth X79 LGA 2011 Intel X79 SATA 6Gb/s USB 3.0 ATX Intel Motherboard', 'price': 314.99 })
db.products.insert({'manufacturer': 'ASUS', 'category':'Desktop Graphics Cards', 'name':'ASUS GTX770-DC2OC-2GD5 GeForce GTX 770 2GB 256-Bit GDDR5 PCI Express 3.0 HDCP Ready SLI Support Video Card', 'price': 329.99 })
db.products.insert({'manufacturer': 'Corsair', 'category':'Desktop Memory', 'name':'CORSAIR Dominator Platinum 16GB (2 x 8GB) 240-Pin DDR3 SDRAM DDR3 2400 Desktop Memory Model CMD16GX3M2A2400C10', 'price': 274.99 })
db.products.insert({'manufacturer': 'Zotac', 'category':'Desktop Graphics Cards', 'name':'ZOTAC ZT-70301-10P GeForce GTX 770 2GB 256-Bit GDDR5 PCI Express 3.0 Video Card', 'price': 371.90 })
db.products.insert({'manufacturer': 'Gigabyte', 'category':'Desktop Graphics Cards', 'name':'GIGABYTE GV-N770OC-4GD GeForce GTX 770 4GB 256-Bit GDDR5 PCI Express 3.0 HDCP Ready WindForce 3X 450W Video Card', 'price': 369.99 })
db.products.insert({'manufacturer': 'Gigabyte', 'category':'Intel Motherboards', 'name':'GIGABYTE GA-Z97X-Gaming G1 WIFI-BK LGA 1150 Intel Z97 HDMI SATA 6Gb/s USB 3.0 ATX Intel Motherboard', 'price': 359.99 })
db.products.insert({'manufacturer': 'Intel', 'category':'Internal SSDs', 'name':'Intel 520 Series Cherryville SSDSC2CW480A310 2.5" 480GB SATA III MLC Internal Solid State Drive (SSD) - OEM', 'price': 299.00 })
db.products.insert({'manufacturer': 'Intel', 'category':'Internal SSDs', 'name':'Intel 730 Series SSDSC2BP480G4R5 2.5" 480GB SATA 6Gb/s MLC Internal Solid State Drive (SSD)', 'price': 399.00 })
db.products.insert({'manufacturer': 'ASUS', 'category':'Intel Motherboards', 'name':'ASUS Z97I-PLUS LGA 1150 Intel Z97 HDMI SATA 6Gb/s USB 3.0 Mini ITX Intel Motherboard', 'price': 159.00 })
db.products.insert({'manufacturer': 'ASUS', 'category':'Desktop Graphics Cards', 'name':'ASUS GTX750TI-OC-2GD5 GeForce GTX 750 Ti 2GB 128-Bit GDDR5 PCI Express 3.0 HDCP Ready Video Card', 'price': 149.99 })
db.products.insert({'manufacturer': 'Corsair', 'category':'Desktop Memory', 'name':'CORSAIR Vengeance Pro 16GB (2 x 8GB) 240-Pin DDR3 SDRAM DDR3 2400 (PC3 19200) Desktop Memory Model CMY16GX3M2A2400C11R', 'price': 174.99 })
db.products.insert({'manufacturer': 'Corsair', 'category':'Power Supplies', 'name':'CORSAIR AXi series AX860i 860W Digital ATX12V / EPS12V SLI Ready CrossFire Ready 80 PLUS PLATINUM Certified Full Modular Active PFC Power Supply New 4th Gen CPU Certified Haswell Ready', 'price': 219.99 })
db.products.insert({'manufacturer': 'Corsair', 'category':'Power Supplies', 'name':'CORSAIR RM Series RM650 650W ATX12V v2.31 and EPS 2.92 80 PLUS GOLD Certified Full Modular Active PFC Power Supply', 'price': 99.99 })
db.products.insert({'manufacturer': 'Zotac', 'category':'Desktop Graphics Cards', 'name':'ZOTAC AMP! ZT-70504-10P GeForce GTX 780 Ti 3GB 384-Bit GDDR5 PCI Express 3.0 x16 HDCP Ready SLI Support Video Card', 'price': 679.99 })
db.products.insert({'manufacturer': 'Gigabyte', 'category':'Desktop Graphics Cards', 'name':'GIGABYTE GV-R929OC-4GD Radeon R9 290 4GB 512-Bit GDDR5 PCI Express 3.0 HDCP Ready Video Card', 'price': 359.99 })
db.products.insert({'manufacturer': 'Gigabyte', 'category':'Intel Motherboards', 'name':'GIGABYTE GA-G1.Sniper Z97 LGA 1150 Intel Z97 HDMI SATA 6Gb/s USB 3.0 ATX Intel Motherboard', 'price': 164.99})
// Let's see how their looks when they are sorted by: Manufacturer then Category
print("[INFO] - Products list, sorted by Manufacturer & Category:");
db.products.find().sort(
{
'manufacturer':1,
'category':1
}
).forEach(printjson);
// Next we want to display highest price of products owned by a manufacturer
// Solution: Use Double Grouping
print("[INFO] - List of highest price of products owned by each manufacturers:");
db.products.aggregate([
{
$group: {
_id: {
manufacturer: "$manufacturer",
category: "$category"
}, max_price: {
$max: "$price"
}
}
},
{
$group: {
_id: "$_id.manufacturer",
max_price: {
$max: "$max_price"
}
}
}
]).shellPrint();
// The minimum price
print("[INFO] - List of lowest price of products owned by each manufacturers:");
db.products.aggregate([
{
$group: {
_id: {
manufacturer: "$manufacturer",
category: "$category"
},
min_price: {
$min: "$price"
}
}
},
{
$group:
{
_id: "$_id.manufacturer",
min_price: {
$min: "$min_price"
}
}
}
]).shellPrint();
// The average price
print("[INFO] - List of average price of products owned by each manufacturers:");
db.products.aggregate([
{
$group: {
_id: {
manufacturer: "$manufacturer",
category: "$category"
},
avg_price: {
$avg: "$price"
}
}
},
{
$group: {
_id: "$_id.manufacturer",
avg_price: {
$avg: "$avg_price"
}
}
}
]).shellPrint();
Raw
10_aggregate_project.js
// We use the latest version of store database as shown in 9_aggregate_double_grouping.js
db = connect("localhost:27017/store");
// Let's say , we want to reshape products list as follow:
// _id -------------> <excluded>
// manufacturer ----> maker <lower case>
// name ------------> item
// category & price-> details: -> Category, price, discounted_price <Discounted price by 10%>
// Also, the outputs are sorted by maker field
print("[INFO] - Display re-shaped products sorted by marker:");
db.products.aggregate([
{
$project: {
_id: 0,
maker: {
$toLower: "$manufacturer"
},
item: "$name",
details: {
category: "$category",
price: "$price",
discounted_price: {
$multiply: [
"$price",
0.90
]
}
}
}
},
{
$sort: {
"maker": 1
}
}
]).forEach(printjson);
Raw
11_aggregate_match_group_project.js
// We use the latest version of store database as shown in 9_aggregate_double_grouping.js
db = connect("localhost:27017/store");
// Let's say, we want to get lowest, highest, average prices for products owned by ASUS along with a list of their product's names
print("[INFO] - Lowest, highest & average prices for Products owned by ASUS:");
db.products.aggregate([
{
$match: {
'manufacturer': "ASUS"
}
},
{
$group: {
_id: "$manufacturer",
products: {
$addToSet: "$name"
},
highest_price: {
$max: "$price"
},
lowest_price: {
$min: "$price"
},
avg_price: {
$avg: "$price"
}
}
},
{
$project: {
_id: 0,
"maker": "$_id",
"products": 1,
"highest_price": 1,
"lowest_price": 1,
"avg_price": 1
}
}
]).forEach(printjson);
Raw
12_aggregate_match_group_project_sort.js
// We use the latest version of store database as shown in 9_aggregate_double_grouping.js
db = connect("localhost:27017/store");
// Let's get a list of Desktop Graphics Cards Manufacturers along with their lowest, highest & average prices of their products
print("[INFO] - List of Desktop Graphics Cards Manufacturers along with their lowest, highest & average prices of their products:");
db.products.aggregate([
{
$match: {
category: "Desktop Graphics Cards"
}
},
{
$group: {
_id: "$manufacturer",
lowest_price: {
$min: "$price"
},
highest_price: {
$max: "$price"
},
avg_price: {
$avg: "$price"
}
}
},
{
$project: {
maker: "$_id",
_id: 0,
lowest_price: "$lowest_price",
highest_price: "$highest_price",
avg_price: "$avg_prive"
}
},
{
$sort: {
maker: 1
}
}
]).forEach(printjson);
Raw
13_aggregate_match_group_project_sort_skip_limit.js
// We use the latest version of store database as shown in 9_aggregate_double_grouping.js
db = connect("localhost:27017/store");
// Modify the query in 12_aggregate_match_group_project_sort.js
print("[INFO] - A Desktop Graphics Cards Manufacturer along with their lowest, highest & average prices of their products :");
db.products.aggregate([
{
$match: {
category: "Desktop Graphics Cards"
}
},
{
$group: {
_id: "$manufacturer",
lowest_price: {
$min: "$price"
},
highest_price: {
$max: "$price"
},
avg_price: {
$avg: "$price"
}
}
},
{
$project: {
maker: "$_id",
_id: 0,
lowest_price: "$lowest_price",
highest_price: "$highest_price",
avg_price: "$avg_prive"
}
},
{
$sort: {
maker: 1
}
},
{
$skip: 1
},
{
$limit: 1
}
]).forEach(printjson);
Raw
14_aggregate_unwind.js
// We use the latest version of travel database as shown in 9_aggregate_double_grouping.js
db = connect("localhost:27017/travel");
var collectionNames = db.getCollectionNames();
for(var i=0; i < collectionNames.length; i++){
if (collectionNames[i] == "hotels"){
print("[INFO] - Hotels collection is exists. Dropping it now.");
db.hotels.drop();
break;
}
}
// Initialise hotels collection
print("[INFO] - Initialise hotels collection...");
db.hotels.insert(
{
'name':'Hotel Okuyumoto',
'country': 'Japan',
'address':'211 Yumotochaya, Hakonemachi, Ashigara-shimogun, Hakone Yumoto, Hakone, Japan 250-0312',
'class':3,
'facilities': ['bar', 'executive floor', 'safety deposit boxes', 'smoking area', 'car park', 'meeting facilities', 'shops', 'elevator', 'restaurant', 'shuttle service', 'wi-fi']
}
);
db.hotels.insert(
{
'name':'Paradise Busan Hotel',
'country': 'Korea',
'address': '1408-5, Joong-dong, Haeundae-gu, Haeundae Beach / BEXCO, Busan, South Korea',
'class':5,
'facilities': ['24-hour room service', 'bar', 'casino', 'elevator', 'laundry service', 'poolside bar', 'safety deposit boxes', 'tours',
'airport transfer', 'business center', 'coffee shop', 'executive floor', 'meeting facilities', 'restaurant', 'salon',
'babysitting', 'car park', 'concierge', 'facilities for disabled guests', 'nightclub', 'room service', 'shops',
'tours', 'sauna', 'fitness centre', 'hot spring bath', 'games room', 'hot tub', 'spa', 'garden', 'massage',
'Wi-fi', 'valet parking']
}
);
// Unwinding the hotels facilities
print("[INFO] - Unwinding the hotels facilities attribute...");
db.hotels.aggregate([
{
$unwind: "$facilities"
}
,
{
$project: {
_id: 0,
name: "$name",
country: "$country",
address: "$address",
class: "$class",
facility: "$facilities"
}
}
]).forEach(printjson);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment