How to combine two collections based on id (transectionid) using node.js?

Question

I have retraining and buying collections that are related to information about transection and purchases, now I want to transform it into a single collection. based on transectionid we need to combine documents

Below are the details of my conversion collection.

{ "transectionid": "1", "transectionamount": "2000", "transectiondate": "2016-07-12 19:22:28", }, { "transectionid": "2", "transectionamount": "1000", "transectiondate": "2016-07-12 20:17:11", } 

below is my build details

 { "purchaseid": "1", "transectionid": "1", "itemprice": "1200", "itemcode": "edcvb", "itemquantity": "1", }, { "purchaseid": "2", "transectionid": "1", "itemprice": "800", "itemcode": "dfgh", "itemquantity": "2", }, { "purchaseid": "3", "transectionid": "2", "itemprice": "1000", "itemcode": "zxcvb", "itemquantity": "1", } 

my expectation result: this is how the order collection {

  "transectionid" : "1", "transectionamount": "2000", "transectiondate": "2016-07-12 19:22:28", "items" : [ { "purchaseid": "1", "itemprice":"1200", "itemcode": "edcvb", "itemquantity": "1", }, { "purchaseid": "2", "itemprice": "800", "itemcode": "dfgh", "itemquantity": "2", } ] } { "transectionid" : "2", "transectionamount": "1000", "transectiondate": "2016-07-12 20:17:11", "items" : [ { "purchaseid": "3", "itemprice":"1000", "itemcode": "zxcvb", "itemquantity": "1", } ] } 

Answer 1

According to your expected result, you may want to get the entire transaction with the completed purchase data as elements.

There is a String in your transectionid structure, and you cannot use ref in your schema. so fill in the transectionid , you should use $lookup because we can define from , localField and foreignField . for this you do not need to use ref in your circuit.

and you must add items: [] to the Transaction schema, because if you have not added, you cannot get the items in your result.

in Transaction controller can add this function to get all transactions with purchase data as elements.

for the from field in $lookup you should use the lowercase form of the collection name, because when creating the mongoose collection made it multiple. like the exported schema name PurchasesData , then it will be created as purchasesdatas

as:

 exports.getTransactions = function(req,res){ Transection.aggregate([ {$lookup:{from:"purchases", localField:"transectionid", foreignField:"transectionid", as:"items"}} ]).exec(function(err, result) { if(err) { console.log('error============', err); return res.status(400).send("error occurred"); }else{ console.log(result); return res.status(200).send(result); } }); }; 

Answer 2

well using javascript, and only a few of the loops you can do this.

 var transactionsSize = transactions.length; var purchasesSize = purchases.length var finalArray = JSON.parse(JSON.stringify(transactions)); for(var i = 0 ; i < transactionsSize; i++ ) { for(var j = 0; j < purchasesSize; j++) { if(transactions[i].transactionid == purchases[j].transactionid) { if(!finalArray[i].items) { finalArray[i].items = []; finalArray[i].items.push(purchases[j]); } else { finalArray[i].items.push(purchases[j]); } } } } 

here is a working fiddle (note that some names do not match your input "transitions", for example) https://jsfiddle.net/yph9yc86/1/

Answer 3

If you use your own mongo driver, you need to implement it yourself.

If you use mongoose, look at the population and even cross DB , as they have already done all the work.

For example, according to your schemes:

 var mongoose = require('mongoose') , Schema = mongoose.Schema var transactionSchema = Schema({ transectiondate: Date, transectionid : Number, transectionamount: Number, items: [{ type: Number, ref: 'Item' }] }); var itemSchema = Schema({ transectionid: Number, purchaseid : Number, itemprice: Number, itemcode: String, itemquantity: Number, }); var Transaction = mongoose.model('Transaction', transactionSchema); var Item = mongoose.model('Item', itemSchema); Transaction .find({}) .populate('items') .exec(function (err, transactinos) { console.log(transactinos); });