lookup on ObjectId s in an array

Question

What s the syntax for doing a  lookup on a field that is an array of ObjectIds rather than just a single ObjectId   Example Order Document        id  ObjectId           products        ObjectId     lt Car ObjectId gt           ObjectId     lt Bike ObjectId gt              Not Working Query   db orders aggregate                 lookup                        from   products              localField   products              foreignField    id              as   productObjects                        Desired Result       id  ObjectId           products        ObjectId     lt Car ObjectId gt           ObjectId     lt Bike ObjectId gt             productObjects          lt Car Object gt          lt Bike Object gt

User · Answer

I have to disagree  we can make  lookup work with IDs array if we preface it with  match stage    x000D   x000D     replace IDs array with lookup results x000D  db products aggregate   x000D         match    products      exists  true        x000D        x000D           lookup    x000D              from   products   x000D              localField   products   x000D              foreignField    id   x000D              as   productObjects  x000D            x000D        x000D     x000D   x000D   x000D    It becomes more complicated if we want to pass the lookup result to a pipeline  But then again there s a way to do so  already suggested by  user12164     x000D   x000D     replace IDs array with lookup results passed to pipeline x000D  db products aggregate   x000D         match    products      exists  true        x000D        x000D           lookup    x000D              from   products   x000D               let    products    products    x000D               pipeline    x000D                      match     expr    in      id      products          x000D                      project    id  0       suppress  id x000D                  x000D              as   productObjects  x000D            x000D        x000D     x000D   x000D   x000D

User · Answer

2017 update   lookup can now directly use an array as the local field   unwind is no longer needed   Old answer    The  lookup aggregation pipeline stage will not work directly with an array  The main intent of the design is for a  left join  as a  one to many  type of join   or really a  lookup    on the possible related data  But the value is intended to be singular and not an array   Therefore you must  de-normalise  the content first prior to performing the  lookup operation in order for this to work  And that means using  unwind   db orders aggregate          Unwind the source         unwind     products            Do the lookup matching         lookup             from    products           localField    products           foreignField     id           as    productObjects                 Unwind the result arrays   likely one or none           unwind     productObjects            Group back to arrays         group               id      id            products       push     products              productObjects       push     productObjects                After  lookup matches each array member the result is an array itself  so you  unwind again and  group to  push new arrays for the final result   Note that any  left join  matches that are not found will create an empty array for the  productObjects  on the given product and thus negate the document for the  product  element when the second  unwind is called   Though a direct application to an array would be nice  it s just how this currently works by matching a singular value to a possible many   As  lookup is basically very new  it currently works as would be familiar to those who are familiar with mongoose as a  poor mans version  of the  populate   method offered there  The difference being that  lookup offers  server side  processing of the  join  as opposed to on the client and that some of the  maturity  in  lookup is currently lacking from what  populate   offers   such as interpolating the lookup directly on an array     This is actually an assigned issue for improvement SERVER-22881  so with some luck this would hit the next release or one soon after   As a design principle  your current structure is neither good or bad  but just subject to overheads when creating any  join   As such  the basic standing principle of MongoDB in inception applies  where if you  can  live with the data  pre-joined  in the one collection  then it is best to do so   The one other thing that can be said of  lookup as a general principle  is that the intent of the  join  here is to work the other way around than shown here  So rather than keeping the  related ids  of the other documents within the  parent  document  the general principle that works best is where the  related documents  contain a reference to the  parent    So  lookup can be said to  work best  with a  relation design  that is the reverse of how something like mongoose  populate   performs it s client side joins  By idendifying the  one  within each  many  instead  then you just pull in the related items without needing to  unwind the array first

User · Answer

You can also use the pipeline stage to perform checks on a sub-docunment array  Here s the example using python  sorry I m snake people    db products aggregate         lookup            from    products          let      pid     products            pipeline                 match       expr       in       id      pid                    Add additional stages here                  as   productObjects           The catch here is to match all objects in the ObjectId array  foreign  id that is in local field prop products    You can also clean up or project the foreign records with additional stages  as indicated by the comment above

User · Answer

Aggregating with  lookup and subsequent  group is pretty cumbersome  so if  and that s a medium if  you re using node  amp  Mongoose or a supporting library with some hints in the schema  you could use a  populate   to fetch those documents   var mongoose   require  mongoose        Schema   mongoose Schema   var productSchema   Schema            var orderSchema   Schema      id       Number    products      type  Schema Types ObjectId  ref   Product           var Product   mongoose model  Product   productSchema   var Order     mongoose model  Order   orderSchema         Order      find           populate  products

User · Answer

use  unwind you will get the first object instead of array of objects   query   db getCollection  vehicles   aggregate            match          status   AVAILABLE         vehicleTypeId             in  Array from newSet d vehicleTypeIds                               lookup          from   servicelocations         localField   locationId         foreignField   serviceLocationId         as   locations                      unwind    locations            result            id    ObjectId  59c3983a647101ec58ddcf90         vehicleId     45680        regionId    1 0       vehicleTypeId     10TONBOX        locationId     100        description     Isuzu 2003-10 Ton Box        deviceId             earliestStart    36000 0       latestArrival    54000 0       status     AVAILABLE        accountId    1 0       locations                id    ObjectId  59c3afeab7799c90ebb3291f             serviceLocationId     100            regionId    1 0           zoneId     DXBZONE1            description     Masafi Park Al Quoz            locationPriority    1 0           accountTypeId    0 0           locationType     DEPOT            location                   makani                     lat    25 123091               lng    55 21082                     deliveryDays     MTWRFSU            timeWindow                   timeWindowTypeId     1                      address1                 address2                 phone                 city                 county                 state                 country                 zipcode                 imageUrl                 contact                   name                     email                           status                 createdBy                 updatedBy                 updateDate                 accountId    1 0           serviceTimeTypeId     1                    id    ObjectId  59c3983a647101ec58ddcf91         vehicleId     81765        regionId    1 0       vehicleTypeId     10TONBOX        locationId     100        description     Hino 2004-10 Ton Box        deviceId             earliestStart    36000 0       latestArrival    54000 0       status     AVAILABLE        accountId    1 0       locations                id    ObjectId  59c3afeab7799c90ebb3291f             serviceLocationId     100            regionId    1 0           zoneId     DXBZONE1            description     Masafi Park Al Quoz            locationPriority    1 0           accountTypeId    0 0           locationType     DEPOT            location                   makani                     lat    25 123091               lng    55 21082                     deliveryDays     MTWRFSU            timeWindow                   timeWindowTypeId     1                      address1                 address2                 phone                 city                 county                 state                 country                 zipcode                 imageUrl                 contact                   name                     email                           status                 createdBy                 updatedBy                 updateDate                 accountId    1 0           serviceTimeTypeId     1

User · Answer

Starting with MongoDB v3 4  released in 2016   the  lookup aggregation pipeline stage can also work directly with an array  There is no need for  unwind any more   This was tracked in SERVER-22881

[mongodb] $lookup on ObjectId's in an array

Examples related to mongodb

Examples related to mongodb-query

Examples related to aggregation-framework