MongoDB Combine data from multiple collections into one how

Question

How can I  in MongoDB  combine data from multiple collections into one collection   Can I use map-reduce and if so then how    I would greatly appreciate some example as I am a novice

User · Answer

You ve to do that in your application layer  If you re using an ORM  it could use annotations  or something similar  to pull references that exist in other collections  I only have worked with Morphia  and the  Reference annotation fetches the referenced entity when queried  so I am able to avoid doing it myself in the code

User · Answer

use multiple  lookup for multiple collections in aggregation  query    db getCollection  servicelocations   aggregate            match          serviceLocationId             in    36728                               lookup          from   orders         localField   serviceLocationId         foreignField   serviceLocationId         as   orders                      lookup          from   timewindowtypes         localField   timeWindow timeWindowTypeId         foreignField   timeWindowTypeId         as   timeWindow                      lookup          from   servicetimetypes         localField   serviceTimeTypeId         foreignField   serviceTimeTypeId         as   serviceTime                      unwind    orders                unwind    serviceTime                limit  14          result            id    ObjectId  59c3ac4bb7799c90ebb3279b         serviceLocationId     36728        regionId    1 0       zoneId     DXBZONE1        description     AL HALLAB REST EMIRATES MALL        locationPriority    1 0       accountTypeId    1 0       locationType     SERVICELOCATION        location               makani                 lat    25 119035           lng    55 198694             deliveryDays     MTWRFSU        timeWindow                               id    ObjectId  59c3b0a3b7799c90ebb32cde                 timeWindowTypeId     1                Description     MORNING                timeWindow                       openTime     06 00                    closeTime     08 00                              accountId    1 0                                     id    ObjectId  59c3b0a3b7799c90ebb32cdf                 timeWindowTypeId     1                Description     MORNING                timeWindow                       openTime     09 00                    closeTime     10 00                              accountId    1 0                                     id    ObjectId  59c3b0a3b7799c90ebb32ce0                 timeWindowTypeId     1                Description     MORNING                timeWindow                       openTime     10 30                    closeTime     11 30                              accountId    1 0                       address1             address2             phone             city             county             state             country             zipcode             imageUrl             contact               name                 email                   status     ACTIVE        createdBy             updatedBy             updateDate             accountId    1 0       serviceTimeTypeId     1        orders                               id    ObjectId  59c3b291f251c77f15790f92                 orderId     AQ18O1704264                serviceLocationId     36728                orderNo     AQ18O1704264                orderDate     18-Sep-17                description     AQ18O1704264                serviceType     Delivery                orderSource     Import                takenBy     KARIM                plannedDeliveryDate    ISODate  2017-08-26T00 00 00 000Z                 plannedDeliveryTime                     actualDeliveryDate                     actualDeliveryTime                     deliveredBy                     size1    296 0               size2    3573 355               size3    240 811               jobPriority    1 0               cancelReason                     cancelDate                     cancelBy                     reasonCode                     reasonText                     status                     lineItems                                              ItemId     BNWB020                        size1    15 0                       size2    78 6                       size3    6 0                                                            ItemId     BNWB021                        size1    20 0                       size2    252 0                       size3    11 538                                                            ItemId     BNWB023                        size1    15 0                       size2    285 0                       size3    16 071                                                            ItemId     CPMW112                        size1    3 0                       size2    25 38                       size3    1 731                                                            ItemId     MMGW001                        size1    25 0                       size2    464 375                       size3    46 875                                                            ItemId     MMNB218                        size1    50 0                       size2    920 0                       size3    60 0                                                            ItemId     MMNB219                        size1    50 0                       size2    630 0                       size3    40 0                                                            ItemId     MMNB220                        size1    50 0                       size2    416 0                       size3    28 846                                                            ItemId     MMNB270                        size1    50 0                       size2    262 0                       size3    20 0                                                            ItemId     MMNB302                        size1    15 0                       size2    195 0                       size3    6 0                                                            ItemId     MMNB373                        size1    3 0                       size2    45 0                       size3    3 75                                               accountId    1 0                                     id    ObjectId  59c3b291f251c77f15790f9d                 orderId     AQ137O1701240                serviceLocationId     36728                orderNo     AQ137O1701240                orderDate     18-Sep-17                description     AQ137O1701240                serviceType     Delivery                orderSource     Import                takenBy     KARIM                plannedDeliveryDate    ISODate  2017-08-26T00 00 00 000Z                 plannedDeliveryTime                     actualDeliveryDate                     actualDeliveryTime                     deliveredBy                     size1    28 0               size2    520 11               size3    52 5               jobPriority    1 0               cancelReason                     cancelDate                     cancelBy                     reasonCode                     reasonText                     status                     lineItems                                              ItemId     MMGW001                        size1    25 0                       size2    464 38                       size3    46 875                                                            ItemId     MMGW001-F1                        size1    3 0                       size2    55 73                       size3    5 625                                               accountId    1 0                                     id    ObjectId  59c3b291f251c77f15790fd8                 orderId     AQ110O1705036                serviceLocationId     36728                orderNo     AQ110O1705036                orderDate     18-Sep-17                description     AQ110O1705036                serviceType     Delivery                orderSource     Import                takenBy     KARIM                plannedDeliveryDate    ISODate  2017-08-26T00 00 00 000Z                 plannedDeliveryTime                     actualDeliveryDate                     actualDeliveryTime                     deliveredBy                     size1    60 0               size2    1046 0               size3    68 0               jobPriority    1 0               cancelReason                     cancelDate                     cancelBy                     reasonCode                     reasonText                     status                     lineItems                                              ItemId     MMNB218                        size1    50 0                       size2    920 0                       size3    60 0                                                            ItemId     MMNB219                        size1    10 0                       size2    126 0                       size3    8 0                                               accountId    1 0                       serviceTime                id    ObjectId  59c3b07cb7799c90ebb32cdc             serviceTimeTypeId     1            serviceTimeType     nohelper            description                 fixedTime    30 0           variableTime    0 0           accountId    1 0

User · Answer

Code snippet  Courtesy-Multiple posts on stack overflow including this one    db cust drop     db zip drop     db cust insert  cust id 1  zip id  101     db cust insert  cust id 2  zip id  101     db cust insert  cust id 3  zip id  101     db cust insert  cust id 4  zip id  102     db cust insert  cust id 5  zip id  102      db zip insert  zip id 101  zip cd  AAA      db zip insert  zip id 102  zip cd  BBB      db zip insert  zip id 103  zip cd  CCC      mapCust   function         var values             cust id  this cust id            emit this zip id  values       mapZip   function         var values         zip cd  this zip cd            emit this zip id  values       reduceCustZip    function k  values        var result           values forEach function value        var field          if   cust id  in value                if     cust ids  in result                     result cust ids                                 result cust ids push value             else       for  field in value            if  value hasOwnProperty field                      result field    value field                                                     return result       db cust zip drop    db cust mapReduce mapCust  reduceCustZip    out     reduce    cust zip      db zip mapReduce mapZip  reduceCustZip    out     reduce    cust zip      db cust zip find      mapCZ   function         var that   this      if   cust ids  in this value            this value cust ids forEach function value                emit value cust id                    zip id  that  id                  zip cd  that value zip cd                                       reduceCZ   function k  values        var result           values forEach function value            var field          for  field in value                if  value hasOwnProperty field                     result field    value field                                       return result     db cust zip joined drop    db cust zip mapReduce mapCZ  reduceCZ    out    cust zip joined      db cust zip joined find   pretty      var flattenMRCollection function dbName collectionName        var collection db getSiblingDB dbName  collectionName        var i 0      var bulk collection initializeUnorderedBulkOp        collection find   value     exists  true      addOption 16  forEach function result            print    i              collection update   id  result  id  result value            bulk find   id  result  id   replaceOne result value            if i 1000  0                        print  Executing bulk                   bulk execute                bulk collection initializeUnorderedBulkOp                          bulk execute         flattenMRCollection  mydb   cust zip joined    db cust zip joined find   pretty

User · Answer

If there is no bulk insert into mongodb  we loop all objects in the small collection and insert them one by one into the big collection   db small collection find   forEach function obj       db big collection insert obj

User · Answer

Mongorestore has this feature of appending on top of whatever is already in the database  so this behavior could be used for combining two collections    mongodump collection1 collection2 rename collection1  mongorestore   Didn t try it yet  but it might perform faster than the map reduce approach

User · Answer

Starting Mongo 4 4  we can achieve this join within an aggregation pipeline by coupling the new  unionWith aggregation stage with  group s new  accumulator operator       gt  db users find           user  1  name   x       user  2  name   y         gt  db books find           user  1  book   a       user  1  book   b       user  2  book   c         gt  db movies find           user  1  movie   g       user  2  movie   h       user  2  movie   i     db users aggregate        unionWith   books           unionWith   movies          group         id    user       user           accumulator            accumulateArgs     name     book     movie            init  function     return   books      movies                  accumulate  function user  name  book  movie              if  name  user name   name            if  book  user books push book             if  movie  user movies push movie             return user                     merge  function userV1  userV2              if  userV2 name  userV1 name   userV2 name            userV1 books concat userV2 books             userV1 movies concat userV2 movies             return userV1                     lang   js                              id  1  user    books    a    b    movies    g    name   x            id  2  user    books    c    movies    h    i    name   y          unionWith combines records from the given collection within documents already in the aggregation pipeline  After the 2 union stages  we thus have all users  books and movies records within the pipeline  We then  group records by  user and accumulate items using the  accumulator operator allowing custom accumulations of documents as they get grouped    the fields we re interested in accumulating are defined with accumulateArgs  init defines the state that will be accumulated as we group elements  the accumulate function allows performing a custom action with a record being grouped in order to build the accumulated state  For instance  if the item being grouped has the book field defined  then we update the books part of the state  merge is used to merge two internal states  It s only used for aggregations running on sharded clusters or when the operation exceeds memory limits

User · Answer

Although you can t do this real-time  you can run map-reduce multiple times to merge data together by using the  reduce  out option in MongoDB 1 8  map reduce  see http   www mongodb org display DOCS MapReduce MapReduce-Outputoptions    You need to have some key in both collections that you can use as an  id   For example  let s say you have a users collection and a comments collection and you want to have a new collection that has some user demographic info for each comment   Let s say the users collection has the following fields     id firstName lastName country gender age   And then the comments collection has the following fields     id userId comment created   You would do this map reduce   var mapUsers  mapComments  reduce  db users comments remove        setup sample data - wouldn t actually use this in production db users remove    db comments remove    db users save  firstName  Rich  lastName  S  gender  M  country  CA  age  18     db users save  firstName  Rob  lastName  M  gender  M  country  US  age  25     db users save  firstName  Sarah  lastName  T  gender  F  country  US  age  13     var users   db users find    db comments save  userId  users 0   id   comment    Hey  what s up    created  new ISODate      db comments save  userId  users 1   id   comment    Not much   created  new ISODate      db comments save  userId  users 0   id   comment    Cool   created  new ISODate         end sample data setup  mapUsers   function         var values             country  this country          gender  this gender          age  this age            emit this  id  values      mapComments   function         var values             commentId  this  id          comment  this comment          created  this created            emit this userId  values      reduce   function k  values        var result       commentFields              commentId                 comment                created                 values forEach function value            var field          if   comment  in value                if     comments  in result                     result comments                                 result comments push value             else if   comments  in value                if     comments  in result                     result comments                                 result comments push apply result comments  value comments                     for  field in value                if  value hasOwnProperty field   amp  amp    field in commentFields                     result field    value field                                       return result     db users mapReduce mapUsers  reduce    out     reduce    users comments      db comments mapReduce mapComments  reduce    out     reduce    users comments      db users comments find   pretty       see the resulting collection   At this point  you will have a new collection called users comments that contains the merged data and you can now use that   These reduced collections all have  id which is the key you were emitting in your map functions and then all of the values are a sub-object inside the value key - the values aren t at the top level of these reduced documents   This is a somewhat simple example   You can repeat this with more collections as much as you want to keep building up the reduced collection   You could also do summaries and aggregations of data in the process   Likely you would define more than one reduce function as the logic for aggregating and preserving existing fields gets more complex   You ll also note that there is now one document for each user with all of that user s comments in an array   If we were merging data that has a one-to-one relationship rather than one-to-many  it would be flat and you could simply use a reduce function like this   reduce   function k  values        var result           values forEach function value            var field          for  field in value                if  value hasOwnProperty field                     result field    value field                                       return result       If you want to flatten the users comments collection so it s one document per comment  additionally run this   var map  reduce  map   function         var debug   function value            var field          for  field in value                print field          value field                         debug this       var that   this      if   comments  in this value            this value comments forEach function value                emit value commentId                    userId  that  id                  country  that value country                  age  that value age                  comment  value comment                  created  value created                                       reduce   function k  values        var result           values forEach function value            var field          for  field in value                if  value hasOwnProperty field                     result field    value field                                       return result     db users comments mapReduce map  reduce    out    comments with demographics       This technique should definitely not be performed on the fly   It s suited for a cron job or something like that which updates the merged data periodically   You ll probably want to run ensureIndex on the new collection to make sure queries you perform against it run quickly  keep in mind that your data is still inside a value key  so if you were to index comments with demographics on the comment created time  it would be db comments with demographics ensureIndex   value created   1

User · Answer

Very basic example with  lookup   db getCollection  users   aggregate                  lookup                from   userinfo               localField   userId               foreignField   userId               as   userInfoData                                  lookup                from   userrole               localField   userId               foreignField   userId               as   userRoleData                          unwind    path    userInfoData   preserveNullAndEmptyArrays  true            unwind    path    userRoleData   preserveNullAndEmptyArrays  true         Here is used       unwind    path    userInfoData   preserveNullAndEmptyArrays  true          unwind    path    userRoleData   preserveNullAndEmptyArrays  true      Instead of     unwind   userRoleData       unwind   userRoleData     Because    unwind   userRoleData   this will return empty or 0 result if no matching record found with  lookup

User · Answer

Yes you can  Take this utility function that I have written today   function shangMergeCol       tcol  db getCollection arguments 0      for  var i 1  i lt arguments length  i         scol  db getCollection arguments i        scol find   forEach          function  d                tcol insert d                           You can pass to this function any number of collections  the first one is going to be the target one  All the rest collections are sources to be transferred to the target one

User · Answer

MongoDB 3 2 now allows one to combine data from multiple collections into one through the  lookup aggregation stage  As a practical example  lets say that you have data about books split into two different collections   First collection  called books  having the following data          isbn    978-3-16-148410-0        title    Some cool book        author    John Doe           isbn    978-3-16-148999-9        title    Another awesome book        author    Jane Roe      And the second collection  called books selling data  having the following data           id   ObjectId  56e31bcf76cdf52e541d9d26         isbn    978-3-16-148410-0        copies sold   12500           id   ObjectId  56e31ce076cdf52e541d9d28         isbn    978-3-16-148999-9        copies sold   720050           id   ObjectId  56e31ce076cdf52e541d9d29         isbn    978-3-16-148999-9        copies sold   1000     To merge both collections is just a matter of using  lookup in the following way   db books aggregate         lookup                from   books selling data               localField   isbn               foreignField   isbn               as   copies sold                  After this aggregation  the books collection will look like the following          isbn    978-3-16-148410-0        title    Some cool book        author    John Doe        copies sold                             id   ObjectId  56e31bcf76cdf52e541d9d26                 isbn    978-3-16-148410-0                copies sold   12500                          isbn    978-3-16-148999-9        title    Another awesome book        author    Jane Roe        copies sold                             id   ObjectId  56e31ce076cdf52e541d9d28                 isbn    978-3-16-148999-9                copies sold   720050                                    id   ObjectId  56e31ce076cdf52e541d9d28                 isbn    978-3-16-148999-9                copies sold   1000                     It is important to note a few things    The  from  collection  in this case books selling data  cannot be sharded  The  as  field will be an array  as the example above  Both  localField  and  foreignField  options on the  lookup stage will be treated as null for matching purposes if they don t exist in their respective collections  the  lookup docs has a perfect example about that     So  as a conclusion  if you want to consolidate both collections  having  in this case  a flat copies sold field with the total copies sold  you will have to work a little bit more  probably using an intermediary collection that will  then  be  out to the final collection

User · Answer

Doing unions in MongoDB in a  SQL UNION  fashion is possible using aggregations along with lookups  in a single query  Here is an example I have tested that works with MongoDB 4 0      Create employees data for testing the union  db getCollection  employees   insert   name   John   type   employee   department   sales      db getCollection  employees   insert   name   Martha   type   employee   department   accounting      db getCollection  employees   insert   name   Amy   type   employee   department   warehouse      db getCollection  employees   insert   name   Mike   type   employee   department   warehouse           Create freelancers data for testing the union  db getCollection  freelancers   insert   name   Stephany   type   freelancer   department   accounting      db getCollection  freelancers   insert   name   Martin   type   freelancer   department   sales      db getCollection  freelancers   insert   name   Doug   type   freelancer   department   warehouse       db getCollection  freelancers   insert   name   Brenda   type   freelancer   department   sales           Here we do a union of the employees and freelancers using a single aggregation query  db getCollection  freelancers   aggregate     1  Use any collection containing at least one document             limit  1       2  Keep only one document of the collection         project     id     REMOVE          3  Remove everything from the document          4  Lookup collections to union together         lookup    from   employees   pipeline      match    department   sales        as   employees              lookup    from   freelancers   pipeline      match    department   sales        as   freelancers               5  Union the collections together with a projection         project    union     concatArrays     employees     freelancers                  6  Unwind and replace root so you end up with a result set         unwind    union            replaceRoot    newRoot    union              Here is the explanation of how it works    Instantiate an aggregate out of any collection of your database that has at least one document in it  If you can t guarantee any collection of your database will not be empty  you can workaround this issue by creating in your database some sort of  dummy  collection containing a single empty document in it that will be there specifically for doing union queries  Make the first stage of your pipeline to be    limit  1    This will strip all the documents of the collection except the first one  Strip all the fields of the remaining document by using a  project stage      project     id     REMOVE       Your aggregate now contains a single  empty document  It s time to add lookups for each collection you want to union together  You may use the pipeline field to do some specific filtering  or leave localField and foreignField as null to match the whole collection      lookup    from   collectionToUnion1   pipeline         as   Collection1          lookup    from   collectionToUnion2   pipeline         as   Collection2          lookup    from   collectionToUnion3   pipeline         as   Collection3       You now have an aggregate containing a single document that contains 3 arrays like this         Collection1             Collection2             Collection3            You can then merge them together into a single array using a  project stage along with the  concatArrays aggregation operator         project             Union       concatArrays     Collection1     Collection2     Collection3            You now have an aggregate containing a single document  into which is located an array that contains your union of collections  What remains to be done is to add an  unwind and a  replaceRoot stage to split your array into separate documents      unwind    Union        replaceRoot    newRoot    Union       Voil    You now have a result set containing the collections you wanted to union together  You can then add more stages to filter it further  sort it  apply skip   and limit    Pretty much anything you want

[mongodb] MongoDB: Combine data from multiple collections into one..how?

Examples related to mongodb

Examples related to mongodb-query

Examples related to aggregation-framework