Script MongoDB Agregación

ScriptMongoDBAgregacion.js

/* 
 * Ejemplo agregación
 */

db.articles.aggregate(
		{"$project" : {"author" : 1}},
		{"$group" : {"_id" : "$author", "count" : {"$sum" : 1}}},
		{"$sort" : {"count" : -1}},
		{"$limit" : 5}
)
    
/*
 * Agregación simple - COUNT
 * 
 * SELECT livesIn, COUNT(*) as total
 * FROM people
 * GROUP BY livesIn
 */

db.people.aggregate([
	{$group:
		{
			_id:'$livesIn',
			total:{$sum: 1}
		}
	}
])


/*
 * Datos de ejemplo para agregación
 */

db.orders.insert({cust_id: 'A123', amount: 500, status: 'A'})
db.orders.insert({cust_id: 'A123', amount: 250, status: 'A'})
db.orders.insert({cust_id: 'B212', amount: 200, status: 'A'})
db.orders.insert({cust_id: 'A123', amount: 300, status: 'D'})

/*
 * Agregación - COUNT
 *  
 * SELECT cust_id, COUNT(*) AS numeroPedidos
 * FROM orders
 * GROUP BY cust_id
 */

db.orders.aggregate([
                     {$group: 
                     	{
                    	 	_id:'$cust_id',
                    	 	numeroPedidos: {$sum: 1}
                     }},
])


/*
 * Agregación - SUM
 *  
 * SELECT cust_id, SUM(amount) AS totalImporte
 * FROM orders
 * GROUP BY cust_id
 */

db.orders.aggregate([
                     {$group: 
                     	{
                    	 	_id:'$cust_id',
                    	 	totalImporte: {$sum: '$amount'}
                     }},
])

/*
 * Agregación - AVG
 *  
 * SELECT cust_id, AVG(amount) AS mediaImporte
 * FROM orders
 * GROUP BY cust_id
 */

db.orders.aggregate([
                     {$group: 
                     	{
                    	 	_id:'$cust_id',
                    	 	mediaImporte: {$avg: '$amount'}
                     }},
])

/*
 * Agregación - MAX
 *  
 * SELECT cust_id, MAX(amount) AS maximoImporte
 * FROM orders
 * GROUP BY cust_id
 */

db.orders.aggregate([
                     {$group: 
                     	{
                    	 	_id:'$cust_id',
                    	 	maximoImporte: {$max: '$amount'}
                     }},
])


/*
 * Agregación - MIN
 *  
 * SELECT cust_id, MIN(amount) AS minimoImporte
 * FROM orders
 * GROUP BY cust_id
 */

db.orders.aggregate([
                     {$group: 
                     	{
                    	 	_id:'$cust_id',
                    	 	minimoImporte: {$min: '$amount'}
                     }},
])

/*
 * Agregación - push 
 * 
 * Estado de los pedidos para cada cliente
 *  
 * No disponible en SQL
 */

db.orders.aggregate([
                     {$group: 
                     	{
                    	 	_id:'$cust_id',
                    	 	estado: {$push: '$status'}
                     }},
])

/*
 * Agregación - addToSet 
 * 
 * Igual que push pero sin duplicados
 * 
 * Estado de los pedidos para cada cliente
 *  
 * No disponible en SQL
 */

db.orders.aggregate([
                     {$group: 
                     	{
                    	 	_id:'$cust_id',
                    	 	estado: {$addToSet: '$status'}
                     }},
])

/* 
 * Agregación - $project
 * 
 * Incluyendo y excluyendo campos
 * 
 */

db.articles.aggregate(
		{$project : {author : 1, _id : 0}}
)

/* 
 * Agregación - $project
 * 
 * Renombrando campos
 * 
 */

db.users.aggregate(
		{$project : {userId : "$_id", _id : 0}}
)

/* 
 * Agregación - $project
 * 
 * Operaciones matemáticas
 * 
 */

db.employees.aggregate(
		{$project : 
			{totalPay : 
				{$subtract : [{$add : ["$salary", "$bonus"]}, "$401k"]}
			}
		}
)

/*
 * Agregación - $project
 * 
 * Uso de fechas
 * 
 */

db.sales.insert({ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:15:39.736Z") })
db.sales.aggregate(
		{$project:
			{
				year: {$year: "$date"},
				month: {$month: "$date"},
				day: { $dayOfMonth: "$date" },
				hour: { $hour: "$date" },
				minutes: { $minute: "$date" },
				seconds: { $second: "$date" },
				milliseconds: { $millisecond: "$date" },
				dayOfYear: { $dayOfYear: "$date" },
				dayOfWeek: { $dayOfWeek: "$date" },
				week: { $week: "$date" }
			}
		}
)

/*
 * Agregación - $project
 * 
 * Operaciones con fechas
 * 
 */

db.sales.aggregate(
		{$project:
			{antiguedad: 
				{$subtract:
					[
					 {$year: new Date()},
					 {$year: "$date"}
					 ]
				}
			}
		}
)

/*
 * Agregación - $project
 * 
 * Uso de condiciones
 * 
 */
{ $cond: [ <boolean-expression>, <true-case>, <false-case> ] }
MongoDB >= 2.6 { $cond: { if: <boolean-expression>, then: <true-case>, else: <false-case-> } }

/* Colección inventory */
{ "_id" : 1, "item" : "abc1", qty: 300 }
{ "_id" : 2, "item" : "abc2", qty: 200 }
{ "_id" : 3, "item" : "xyz1", qty: 250 }

db.inventory.insert({ "_id" : 1, "item" : "abc1", qty: 300 })
db.inventory.insert({ "_id" : 2, "item" : "abc2", qty: 200 })
db.inventory.insert({ "_id" : 3, "item" : "xyz1", qty: 250 })

db.inventory.aggregate(
   [
      {
         $project:
           {
             item: 1,
             discount:
               {
                 $cond: [{ $gte: [ "$qty", 250 ] }, 30, 20]
               }
           }
      }
   ]
)

/*
 * Agregación - $project 
 * 
 * Para limpiar, eliminar o transformar documentos

 */

db.orders.aggregate([
                     {$project: 
                     	{
                    	 	_id:0,
                    	 	cliente:{$toLower: '$cust_id'},
                    	 	cantidadEnDolares: {$multiply: ['$amount', 1.1]}
                     	}}
])

/*
 * Agregación - $project y $group 
 * 
 */

db.orders.aggregate([
                     {$project: 
                     	{
                    	 	_id:0,
                    	 	cust_id: 1,
                    	 	cantidadEnDolares: {$multiply: ['$amount', 1.1]},
                     	}},
                     {$group:
                    	 {
                    	 	_id: '$cust_id',
                    	 	totalEnDolares: {$sum: '$cantidadEnDolares'} 
                    	 }
                     }
])
/*
 * Agregación compuesta
 * 
 * SELECT cust_id, status, COUNT(*) as totalPedidos
 * FROM orders
 * GROUP BY cust_id, status
 */

db.orders.aggregate([
        {$group: {
        _id: {
         	'customer_id': '$cust_id', 
            'status': '$status'
        }, 
        totalPedidos: {$sum: 1}
    }}
])

/*
 * Agregación - Filtrado previo con $match
 * 
 * SELECT cust_id, SUM(amount) AS total
 * FROM orders
 * WHERE status = 'A'
 * GROUP BY cust_id
 */


db.orders.aggregate([
                     {$match: 	{status: 'A'}},
                     {$group:	
                     	{
                    	 	_id:'$cust_id',
                    	 	total: {$sum: '$amount'}
                     	}
                     }
])

/*
 * Agregación - Filtrado a posteriori con $match
 * 
 * SELECT cust_id, COUNT(*) AS totalPedidos
 * FROM orders
 * GROUP BY cust_id
 * HAVING SUM(amount) > 1
 */

db.orders.aggregate([
                     {$group:	
                     	{
                    	 	_id:'$cust_id',
                    	 	totalPedidos: {$sum: 1}
                     	}
                     },
                     {$match: 	{totalPedidos: {$gt: 1}}}
])


/*
 * Agregación - Filtrado previo y a posteriori con $match
 * 
 * SELECT cust_id, COUNT(*) AS totalPedidos
 * FROM orders
 * WHERE status = 'A'
 * GROUP BY cust_id
 * HAVING SUM(amount) > 1
 */

db.orders.aggregate([
                     {$match: 	{status: 'A'}},
                     {$group:	
                     	{
                    	 	_id:'$cust_id',
                    	 	totalPedidos: {$sum: 1}
                     	}
                     },
                     {$match: 	{totalPedidos: {$gt: 1}}}
])

/*
 * Agregación - $sort
 * 
 * Sintaxis: { $sort: { <field1>: <sort order>, <field2>: <sort order> ... } }
 * 
 */

db.scores.aggregate([
	{$group: 
		{
			_id:'$student_id', 
			calificacion: {$sum: "$score"}
		}
   },
   {$sort: 
   	{
	   calificacion: -1, 
	   student_id: 1
   	}
   }
])

/* 
 * Agregación - $skip y $limit
 * 
 * Sintaxis: { $skip: <positive integer> }
 * Sintaxis: { $limit: <positive integer> }
 */

db.scores.aggregate([
	{$group: 
		{
			_id:'$student_id', 
			calificacion: {$sum: "$score"}
		}
   },
   {$sort: 
   	{
	   calificacion: -1, 
	   student_id: 1
   	}
   },
   {$skip: 10},
   {$limit: 3}
])
/* 
 * Agregación - Desanidar con $unwind
 * 
 */
Colección inventory: { "_id" : 4, "item" : "ABC1", sizes: [ "S", "M", "L"] }

db.inventory.insert({ "_id" : 4, "item" : "ABC1", sizes: [ "S", "M", "L"] })
db.inventory.aggregate([
   {$unwind: "$sizes"}
])

var samplePost = 
{
	"_id" : ObjectId("50eeffc4c82a5271290530be"),
	"author" : "k",
	"post" : "Hello, world!",
	"comments" : [
	   {
		   "author" : "mark",
		   "date" : ISODate("2013-01-10T17:52:04.148Z"),
		   "text" : "Nice post"
	   }, 
	   {
		   "author" : "bill",
		   "date" : ISODate("2013-01-10T17:52:04.148Z"),
		   "text" : "I agree"
	   }
	]
}

db.blog.insert(samplePost)

db.blog.aggregate([
   {$unwind: "$comments"}
])

{
	"_id" : ObjectId("50eeffc4c82a5271290530be"),
	"author" : "k",
	"post" : "Hello, world!",
	"comments" : {
		"author" : "mark",
		"date" : ISODate("2013-01-10T17:52:04.148Z"),
		"text" : "Nice post"
	}
},
{
	"_id" : ObjectId("50eeffc4c82a5271290530be"),
	"author" : "k",
	"post" : "Hello, world!",
	"comments" : {
		"author" : "bill",
		"date" : ISODate("2013-01-10T17:52:04.148Z"),
		"text" : "I agree"
	}
}

db.blog.aggregate([
   {$unwind: "$comments"},
   {$match: {"comments.author": "mark"}}
])

{
	"_id" : ObjectId("50eeffc4c82a5271290530be"),
	"author" : "k",
	"post" : "Hello, world!",
	"comments" : {
		"author" : "mark",
		"date" : ISODate("2013-01-10T17:52:04.148Z"),
		"text" : "Nice post"
	}
}

db.blog.find({"comments.author":"mark"}).pretty()

{
	"_id" : ObjectId("50eeffc4c82a5271290530be"),
	"author" : "k",
	"post" : "Hello, world!",
	"comments" : [
		{
			"author" : "mark",
			"date" : ISODate("2013-01-10T17:52:04.148Z"),
			"text" : "Nice post"
		},
		{
			"author" : "bill",
			"date" : ISODate("2013-01-10T17:52:04.148Z"),
			"text" : "I agree"
		}
	]
}