Skip to content

Instantly share code, notes, and snippets.

@saintc0d3r

saintc0d3r/10_aggregate_project.js

Last active August 29, 2015 14:03
Show Gist options
  • Save saintc0d3r/58aaf7d93fbcc158306a to your computer and use it in GitHub Desktop.
Save saintc0d3r/58aaf7d93fbcc158306a to your computer and use it in GitHub Desktop.
Download ZIP
[MongoDb][MongoShell] Aggregate Queries in MongoDb ( $sum, $avg, $addToSet, $push, $max, $min, double grouping, $match, $project, $sort, $limit, $skip)
Raw
10_aggregate_project.js
// We use the latest version of store database as shown in 9_aggregate_double_grouping.js
// 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
db.products.aggregate([
{
$project: {
_id: 0,
maker: {
$toLower: "$manufacturer"
},
item: "$name",
details: {
category: 1,
price: 1,
discounted_price: {
$multiply: [
"$price",
0.90
]
}
}
}
},
{
$sort: {
"maker": 1
}
}
])
/* The output should be looked like this:
{
"maker": "asus",
"item": "ASUS Sabertooth X79 LGA 2011 Intel X79 SATA 6Gb/s USB 3.0 ATX Intel Motherboard",
"details": {
"category": "Intel Motherboards",
"price": 314.99,
"discounted_price": 283.49100000000004
}
}{
"maker": "asus",
"item": "ASUS GTX770-DC2OC-2GD5 GeForce GTX 770 2GB 256-Bit GDDR5 PCI Express 3.0 HDCP Ready SLI Support Video Card",
"details": {
"category": "Desktop Graphics Cards",
"price": 329.99,
"discounted_price": 296.99100000000004
}
}{
"maker": "asus",
"item": "ASUS Z97I-PLUS LGA 1150 Intel Z97 HDMI SATA 6Gb/s USB 3.0 Mini ITX Intel Motherboard",
"details": {
"category": "Intel Motherboards",
"price": 159,
"discounted_price": 143.1
}
}{
"maker": "asus",
"item": "ASUS GTX750TI-OC-2GD5 GeForce GTX 750 Ti 2GB 128-Bit GDDR5 PCI Express 3.0 HDCP Ready Video Card",
"details": {
"category": "Desktop Graphics Cards",
"price": 149.99,
"discounted_price": 134.991
}
}{
"maker": "corsair",
"item": "CORSAIR Dominator Platinum 16GB (2 x 8GB) 240-Pin DDR3 SDRAM DDR3 2400 Desktop Memory Model CMD16GX3M2A2400C10",
"details": {
"category": "Desktop Memory",
"price": 274.99,
"discounted_price": 247.491
}
}{
"maker": "corsair",
"item": "CORSAIR Vengeance Pro 16GB (2 x 8GB) 240-Pin DDR3 SDRAM DDR3 2400 (PC3 19200) Desktop Memory Model CMY16GX3M2A2400C11R",
"details": {
"category": "Desktop Memory",
"price": 174.99,
"discounted_price": 157.491
}
}{
"maker": "corsair",
"item": "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",
"details": {
"category": "Power Supplies",
"price": 219.99,
"discounted_price": 197.991
}
}{
"maker": "corsair",
"item": "CORSAIR RM Series RM650 650W ATX12V v2.31 and EPS 2.92 80 PLUS GOLD Certified Full Modular Active PFC Power Supply",
"details": {
"category": "Power Supplies",
"price": 99.99,
"discounted_price": 89.991
}
}{
"maker": "gigabyte",
"item": "GIGABYTE GV-N770OC-4GD GeForce GTX 770 4GB 256-Bit GDDR5 PCI Express 3.0 HDCP Ready WindForce 3X 450W Video Card",
"details": {
"category": "Desktop Graphics Card",
"price": 369.99,
"discounted_price": 332.99100000000004
}
}{
"maker": "gigabyte",
"item": "GIGABYTE GA-Z97X-Gaming G1 WIFI-BK LGA 1150 Intel Z97 HDMI SATA 6Gb/s USB 3.0 ATX Intel Motherboard",
"details": {
"category": "Intel Motherboards",
"price": 359.99,
"discounted_price": 323.99100000000004
}
}{
"maker": "gigabyte",
"item": "GIGABYTE GV-R929OC-4GD Radeon R9 290 4GB 512-Bit GDDR5 PCI Express 3.0 HDCP Ready Video Card",
"details": {
"category": "Desktop Graphics Card",
"price": 359.99,
"discounted_price": 323.99100000000004
}
}{
"maker": "gigabyte",
"item": "GIGABYTE GA-G1.Sniper Z97 LGA 1150 Intel Z97 HDMI SATA 6Gb/s USB 3.0 ATX Intel Motherboard",
"details": {
"category": "Intel Motherboards",
"price": 164.99,
"discounted_price": 148.491
}
}{
"maker": "intel",
"item": "Intel Core i7-4960X Ivy Bridge-E 6-Core 3.6GHz (Turbo 4GHz) LGA 2011 130W Desktop Processor BX80633i74960X",
"details": {
"category": "CPUs-Processors-Desktop",
"price": 1049.99,
"discounted_price": 944.991
}
}{
"maker": "intel",
"item": "Intel Core i7-4790 Haswell Quad-Core 3.6GHz LGA 1150 84W Desktop Processor Intel HD Graphics 4600 BX80646I74790",
"details": {
"category": "CPUs-Processors-Desktop",
"price": 314.99,
"discounted_price": 283.49100000000004
}
}{
"maker": "intel",
"item": "Intel 520 Series Cherryville SSDSC2CW480A310 2.5\" 480GB SATA III MLC Internal Solid State Drive (SSD) - OEM",
"details": {
"category": "Internal SSDs",
"price": 299,
"discounted_price": 269.1
}
}{
"maker": "intel",
"item": "Intel 730 Series SSDSC2BP480G4R5 2.5\" 480GB SATA 6Gb/s MLC Internal Solid State Drive (SSD)",
"details": {
"category": "Internal SSDs",
"price": 399,
"discounted_price": 359.1
}
}{
"maker": "zotac",
"item": "ZOTAC ZT-70301-10P GeForce GTX 770 2GB 256-Bit GDDR5 PCI Express 3.0 Video Card",
"details": {
"category": "Desktop Graphics Card",
"price": 371.9,
"discounted_price": 334.71
}
}{
"maker": "zotac",
"item": "ZOTAC AMP! ZT-70504-10P GeForce GTX 780 Ti 3GB 384-Bit GDDR5 PCI Express 3.0 x16 HDCP Ready SLI Support Video Card",
"details": {
"category": "Desktop Graphics Card",
"price": 679.99,
"discounted_price": 611.991
}
}
*/
Raw
11_match_group_project.js
// We use the latest version of store database as shown in 9_aggregate_double_grouping.js
// 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
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
}
}
])
// Output
/*
{
"products": [
"ASUS Z97I-PLUS LGA 1150 Intel Z97 HDMI SATA 6Gb/s USB 3.0 Mini ITX Intel Motherboard",
"ASUS GTX770-DC2OC-2GD5 GeForce GTX 770 2GB 256-Bit GDDR5 PCI Express 3.0 HDCP Ready SLI Support Video Card",
"ASUS GTX750TI-OC-2GD5 GeForce GTX 750 Ti 2GB 128-Bit GDDR5 PCI Express 3.0 HDCP Ready Video Card",
"ASUS Sabertooth X79 LGA 2011 Intel X79 SATA 6Gb/s USB 3.0 ATX Intel Motherboard"
],
"highest_price": 329.99,
"lowest_price": 149.99,
"avg_price": 238.4925,
"maker": "ASUS"
}
*/
Raw
12_aggregate_match_group_project_sort.js
// We use the latest version of store database as shown in 9_aggregate_double_grouping.js
// Let's get a 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: 1,
highest_price: 1,
avg_price: 1
}
},
{
$sort: {
maker: 1
}
}
])
/* Outputs:
"lowest_price" : 149.99, "highest_price" : 329.99, "avg_price" : 239.99, "maker" : "ASUS" }
"lowest_price" : 359.99, "highest_price" : 369.99, "avg_price" : 364.99, "maker" : "Gigabyte" }
"lowest_price" : 371.9, "highest_price" : 679.99, "avg_price" : 525.9449999999999, "maker" : "Zotac" }
*/
Raw
13_aggregate_sort_skip_limit.js
// Modify the query in 12_aggregate_match_group_project_sort.js so that it would rid ASUS & Zotac then left Gigabyte's as the only one result
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: 1,
highest_price: 1,
avg_price: 1
}
},
{
$sort: {
maker: 1
}
},
{
$skip: 1
},
{
$limit: 1
}
])
/*
Outputs result:
{ "lowest_price" : 359.99, "highest_price" : 369.99, "avg_price" : 364.99, "maker" : "Gigabyte" }
*/
Raw
15_aggregate_double_unwind.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] == "clothes"){
print("[INFO] - clothes collection is exists. Dropping it now.");
db.clothes.drop();
break;
}
}
// And we want to use store to keep our products information
print("[INFO] - Initialise clothes collection..");
db.clothes.insert({'name': 'Polo Shirt', 'sizes': ["Small", "Medium", "Large"], 'colours': ['navy', 'white', 'orange', 'red']});
db.clothes.insert({'name': 'T-Shirt', 'sizes': ["Small", "Medium", "Large", "X-Large"], 'colours': ['navy', 'black', 'orange', 'red']});
db.clothes.insert({'name': 'Denim Pants', 'sizes': ["32x32", "31x30", "36x32"], 'colours': ['navy', 'white', 'orange', 'violet']});
// Let's see how their looks when they are sorted by: Name
print("[INFO] - Clothes list, sorted by Name:");
db.clothes.find().sort(
{
'name':1
}
).forEach(printjson);
// Next we want to double unwind the clothes documents by sizes then by colours
print("[INFO] - Unwinded clothes by sizes then by colours:");
var aggregate_query = [
{
$unwind: "$sizes"
},
{
$unwind: "$colours"
}
];
db.clothes.aggregate(aggregate_query).forEach(printjson);
// On these unwinded clothes, let's get the number of cloth types grouped by size & colour
print("[INFO] - Number of cloth types grouped by size & colour...");
aggregate_query.push( {
$group:{
_id: {'size':"$sizes", 'colour': "$colours"},
count: {$sum: 1}
}
} );
aggregate_query.push( {
$sort: {
"size":1,
"colour":1
}
});
db.clothes.aggregate(aggregate_query).forEach(printjson);
Raw
1_aggregate_sum.js
// Let's say we have a store database
use store
// And we want to use store to keep our products information
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:
db.products.aggregate([{$group: {_id: "$manufacturer", 'num_products': {$sum:1} }}])
// The output should be looked like this:
/*
{ "_id" : "Gigabyte", "num_products" : 2 }
{ "_id" : "Zotac", "num_products" : 1 }
{ "_id" : "Corsair", "num_products" : 1 }
{ "_id" : "ASUS", "num_products" : 2 }
{ "_id" : "Intel", "num_products" : 2 }
{ "_id" : null, "num_products" : 5 }
*/
// Count number of products by category
db.products.aggregate([{$group: {_id: "$category", 'num_products': {$sum:1} }}])
// The output should be looked like this:
/*
{ "_id" : "Desktop Graphics Card", "num_products" : 4 }
{ "_id" : "Desktop Memory", "num_products" : 2 }
{ "_id" : "Desktop Graphics Cards", "num_products" : 1 }
{ "_id" : "Intel Motherboards", "num_products" : 3 }
{ "_id" : "CPUs-Processors-Desktop", "num_products" : 3 }
*/
Raw
2_aggregate_compound.js
// Let's say we have a database foo where it has bar collection as follow:
use foo
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
db.bar.aggregate([{$group: {_id: {'Alpha': "$a", 'Bravo': "$b", 'Charlie': "$c" }, 'quantity': {$sum:1} }}])
// The output should be looked like these:
/*
{ "_id" : { "Alpha" : 3, "Bravo" : 5, "Charlie" : 3 }, "quantity" : 1 }
{ "_id" : { "Alpha" : 3, "Bravo" : 3, "Charlie" : 2 }, "quantity" : 2 }
{ "_id" : { "Alpha" : 3, "Bravo" : 3, "Charlie" : 1 }, "quantity" : 1 }
{ "_id" : { "Alpha" : 2, "Bravo" : 2, "Charlie" : 1 }, "quantity" : 1 }
{ "_id" : { "Alpha" : 1, "Bravo" : 1, "Charlie" : 1 }, "quantity" : 1 }
*/
Raw
3_compound_id.js
// A document's id could be made from 2 or more keys.
// Let's say we have a database foo where it has bar collection as follow:
use foo
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}})
// Failed. Try another chance
db.bar.find({_id: {b:1}})
// Failed. Last chance
db.bar.find({_id: {a:2, b:2}})
// Got it. The result should be looked like this:
// { "_id" : { "a" : 2, "b" : 2 }, "c" : "bravo" }
Raw
4_aggregate_sum_again.js
// Let's say we have a states database with zip collection in it
use states
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
db.zips.aggregate([{$group: {_id: "$state", "population": {$sum: "$pop"} }}])
Raw
5_aggregate_avg.js
// Let's say we have a store database where it has products collection as can be seen in 1_aggregate_sum.js
// Then, we want to get a list of average price of these products grouped by category & manufacturer.
db.products.aggregate([{$group: {_id: "$category", 'average_price': {$avg: "$price"} } }])
/* Query's Output:
{ "_id" : "Desktop Graphics Card", "average_price" : 370.945 }
{ "_id" : "Desktop Memory", "average_price" : 274.99 }
{ "_id" : "Desktop Graphics Cards", "average_price" : 329.99 }
{ "_id" : "Intel Motherboards", "average_price" : 337.49 }
{ "_id" : "CPUs-Processors-Desktop", "average_price" : 682.49 }
*/
db.products.aggregate([{$group: {_id: "$manufacturer", 'average_price': {$avg: "$price"} } }])
Raw
6_aggregate_addToSet.js
// Let's say we have a store database where it has products collection as can be seen in 1_aggregate_sum.js
// Then, we want to get a list of Manufacturers along with their product categories
db.products.aggregate([{$group: {_id: "$manufacturer", categories: {$addToSet: "$category"}}}])
/* The query's output should be looked like this:
{ "_id" : "Gigabyte", "categories" : [ "Intel Motherboards", "Desktop Graphics Card" ] }
{ "_id" : "Zotac", "categories" : [ "Desktop Graphics Card" ] }
{ "_id" : "Corsair", "categories" : [ "Desktop Memory" ] }
{ "_id" : "ASUS", "categories" : [ "Desktop Graphics Cards", "Intel Motherboards" ] }
{ "_id" : "Intel", "categories" : [ "CPUs-Processors-Desktop" ] }
*/
Raw
7_aggregate_push.js
// $push is similar to $addToSet. Except, it lets the duplicated result intact.
db.products.aggregate([{$group: {_id: "$manufacturer", categories: {$push: "$category"}}}])
/* The query's output should be looked like this (notice the categories belong to 'Intel'):
{ "_id" : "Gigabyte", "categories" : [ "Desktop Graphics Card", "Intel Motherboards" ] }
{ "_id" : "Zotac", "categories" : [ "Desktop Graphics Card" ] }
{ "_id" : "Corsair", "categories" : [ "Desktop Memory" ] }
{ "_id" : "ASUS", "categories" : [ "Intel Motherboards", "Desktop Graphics Cards" ] }
{ "_id" : "Intel", "categories" : [ "CPUs-Processors-Desktop", "CPUs-Processors-Desktop" ] }
*/
Raw
8_aggregate_min_max.js
// Let's say we have a store database where it has products collection as can be seen in 1_aggregate_sum.js
// Then, we want to get a list of Manufactorers along with maximum price of one of their products
db.products.aggregate([{$group: {_id: "$manufacturer", max_price: {$max: "$price"}}}])
/* The query's output should be looked like this:
{ "_id" : "Gigabyte", "max_price" : 369.99 }
{ "_id" : "Zotac", "max_price" : 371.9 }
{ "_id" : "Corsair", "max_price" : 274.99 }
{ "_id" : "ASUS", "max_price" : 329.99 }
{ "_id" : "Intel", "max_price" : 1049.99 }
*/
// Get the minimum price of products owned by each manufacturer
db.products.aggregate([{$group: {_id: "$manufacturer", min_price: {$min: "$price"}}}])
// Looking at the above result, the result still miss the product name whose max price is returned.
// Question: How to display the product's name ?
Raw
9_aggregate_double_grouping.js
// Using store database in prior examples, let's add some new items in there
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 sorted by: Manufacturer then Category
db.products.find().sort({'manufacturer':1, 'category':1}).pretty()
// NExt we want to display highest price of products owned by a manufacturer
// Solution: Use Double Grouping
db.products.aggregate([{$group: {_id: {manufacturer: "$manufacturer", category: "$category"}, max_price: {$max: "$price"} }},
{$group: {_id: "$_id.manufacturer", max_price: {$max: "$max_price"}}}])
// The minimum price
db.products.aggregate([{$group: {_id: {manufacturer: "$manufacturer", category: "$category"}, min_price: {$min: "$price"} }},
{$group: {_id: "$_id.manufacturer", min_price: {$min: "$min_price"}}}])
// The average price
db.products.aggregate([{$group: {_id: {manufacturer: "$manufacturer", category: "$category"}, avg_price: {$avg: "$price"} }},
{$group: {_id: "$_id.manufacturer", avg_price: {$avg: "$avg_price"}}}])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment