MongoDB vs SQL :Group By Query Side By Side

We will look into group by query in mongoDB side side with Mysql

so that as we are familiar with MySQL we can relate bit easier.

First Insert this documents into mongo db:

db.sales.insertMany([

{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") },

{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") },

{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") },

{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") },

{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:12:00Z") }

])

On parrellel we will create equivalent mysql table and insert same data there

create table sales(

id integer auto_increment primary key,

item varchar(50),

price float,

quantity int,

date datetime

)

INSERT INTO sales(item,price,quantity,date) values('abc',10,2,now());

INSERT INTO sales(item,price,quantity,date) values('jkl',20,1,now());

INSERT INTO sales(item,price,quantity,date) values('xyz',5,5,now());

INSERT INTO sales(item,price,quantity,date) values('abc',10,10,now());

INSERT INTO sales(item,price,quantity,date) values('xyz',5,10,now());

Group by only one field:

Mongo Query:

db.sales.aggregate([

{

$group:

{

_id:"$item",

avg:{$avg:"$quantity"}

}

}

])

Output:

[

{ _id: 'abc', avg: 6 },

{ _id: 'xyz', avg: 7.5 },

{ _id: 'jkl', avg: 1 }

]

Equvalent Mysql Query:

select item,avg(quantity) from sales group by item;

Output:

+------+---------------+

| item | avg(quantity) |

+------+---------------+

| abc | 6.0000 |

| jkl | 1.0000 |

| xyz | 7.5000 |

+------+---------------+

Group by multiple fields:

Mongo Query:

db.sales.aggregate([

{

$group:

{

_id:{"item":"$item","price":"$price"},

avg:{$avg:"$quantity"}

}

}

])

Output:

[

{ _id: { item: 'xyz', price: 5 }, avg: 7.5 },

{ _id: { item: 'jkl', price: 20 }, avg: 1 },

{ _id: { item: 'abc', price: 10 }, avg: 6 }

]

MySql Query:

select item,price,avg(quantity) from sales group by item,price;

Output:

+------+-------+---------------+

| item | price | avg(quantity) |

+------+-------+---------------+

| abc | 10 | 6.0000 |

| jkl | 20 | 1.0000 |

| xyz | 5 | 7.5000 |

+------+-------+---------------+

Pushing column to Array in Group By:

Mongo Query:

db.sales.aggregate([

{

$group:

{

_id:{"item":"$item","price":"$price"},

qty:{$push:"$quantity"}

}

}

])

Output:

[

{ _id: { item: 'abc', price: 10 }, qty: [ 2, 10 ] },

{ _id: { item: 'jkl', price: 20 }, qty: [ 1 ] },

{ _id: { item: 'xyz', price: 5 }, qty: [ 5, 10 ] }

]

Mysql Query:

select item,price,JSON_ARRAYAGG(quantity) from sales group by item,price;

Output:

+------+-------+-------------------------+

| item | price | JSON_ARRAYAGG(quantity) |

+------+-------+-------------------------+

| abc | 10 | [2, 10] |

| jkl | 20 | [1] |

| xyz | 5 | [5, 10] |

+------+-------+-------------------------+

Sum of quantity:

Mongo Query:

db.sales.aggregate([

{

$group:

{

_id:{"item":"$item","price":"$price"},

sum:{$sum:"$quantity"}

}

}

])

Output:

[

{ _id: { item: 'xyz', price: 5 }, sum: 15 },

{ _id: { item: 'jkl', price: 20 }, sum: 1 },

{ _id: { item: 'abc', price: 10 }, sum: 12 }

]

Mysql Query:

select item,price,sum(quantity) from sales group by item,price;

Output:

+------+-------+---------------+

| item | price | sum(quantity) |

+------+-------+---------------+

| abc | 10 | 12 |

| jkl | 20 | 1 |

| xyz | 5 | 15 |

+------+-------+---------------+

count of

MongoDb Query:

db.sales.aggregate([

{

$group:

{

_id:{"item":"$item","price":"$price"},

count:{$sum:1}

}

}

])

Output:

[

{ _id: { item: 'xyz', price: 5 }, count: 2 },

{ _id: { item: 'jkl', price: 20 }, count: 1 },

{ _id: { item: 'abc', price: 10 }, count: 2 }

]

MySql Query:

select item,price,count(*) from sales group by item,price;

select item,price,count(item) from sales group by item,price;

select item,price,count(price) from sales group by item,price;

Output:

+------+-------+----------+

| item | price | count(*) |

+------+-------+----------+

| abc | 10 | 2 |

| jkl | 20 | 1 |

| xyz | 5 | 2 |

+------+-------+----------+