Implement a query similar to MySQL Union with MongoDB?

For a similar query to UNION two collections, use JOIN in MongoDB along with aggregate(). Let us create a collection with documents −

> db.demo486.insertOne({_id:1,"Amount":30,"No":4});
{ "acknowledged" : true, "insertedId" : 1 }
> db.demo486.insertOne({_id:2,"Amount":40,"No":2});
{ "acknowledged" : true, "insertedId" : 2 }
> db.demo486.insertOne({_id:3,"Amount":60,"No":6});
{ "acknowledged" : true, "insertedId" : 3 }

Display all documents from a collection with the help of find() method −

> db.demo486.find();

This will produce the following output −

{ "_id" : 1, "Amount" : 30, "No" : 4 }
{ "_id" : 2, "Amount" : 40, "No" : 2 }
{ "_id" : 3, "Amount" : 60, "No" : 6 }

Following is the query to create second collection with documents −

> db.demo487.insertOne({_id:1,"Price":10,"No":4});
{ "acknowledged" : true, "insertedId" : 1 }
> db.demo487.insertOne({_id:2,"Price":80,"No":9});
{ "acknowledged" : true, "insertedId" : 2 }
> db.demo487.insertOne({_id:3,"Price":20,"No":6});
{ "acknowledged" : true, "insertedId" : 3 }

Display all documents from a collection with the help of find() method −

> db.demo487.find();

This will produce the following output −

{ "_id" : 1, "Price" : 10, "No" : 4 }
{ "_id" : 2, "Price" : 80, "No" : 9 }
{ "_id" : 3, "Price" : 20, "No" : 6 }

Following is the query to UNION two queries in MongoDB −

> db.getCollection('demo486').aggregate([
... {$lookup : { from : "demo487",localField : "No", foreignField :"No", as:"demo487"}},
... {$unwind : "$demo487"},
... {
...    $group : {
...       _id : {
...          No :"$No",
...       },
...       TotalValue : { $sum : { $add: [ "$Amount", "$demo487.Price" ] }}
...    }
... },
... {$sort : {"_id.No":1}},
... {
...    $project : {
...       No :"$_id.No",
...       TotalValue : 1,
...       _id : 0
...    }
... }
... ])

This will produce the following output −

{ "TotalValue" : 40, "No" : 4 }
{ "TotalValue" : 80, "No" : 6 }

AmitDiwan

Published on 11-May-2020 09:59:51

• Related Questions & Answers
• How to implement MongoDB $or query?
• MongoDB query to implement aggregate function
• How to query MongoDB similar to “like” ?
• How to do a count on a MySQL union query?
• Implement WHERE IN vs OR in MySQL with similar example
• MongoDB Query to implement $in in array
• MongoDB query to implement nor query to fetch documents except a specific document
• Select the table name as a column in a UNION select query with MySQL?
• Query in MongoDB to perform an operation similar to LIKE operation
• MySQL UNION SELECT and IN clause in a single query
• Use UNION ALL to insert records in two tables with a single query in MYSQL
• MongoDB query to implement OR operator in find()
• How to query MongoDB with a LIMIT?
• MySQL query to calculate sum from 5 tables with a similar column named “UP”?
• MongoDB aggregation to sum product price with similar IDs