[SERVER-22881]  lookup between local (multiple)array of values and foreign (single) value Created: 26/Feb/16  Updated: 27/May/19  Resolved: 29/Mar/16

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: 3.3.4

Type: Improvement Priority: Major - P3
Reporter: Jose Antonio Illescas Olmo Assignee: Charlie Swanson
Resolution: Done Votes: 0
Labels: expression
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Documented
is documented by DOCS-8930 lookup between local (multiple)array... Closed
Duplicate
is duplicated by SERVER-21469 $lookup is not working in case of the... Closed
is duplicated by SERVER-23180 array in $lookup localField should n... Closed
Related
related to DOCS-12756 Update documentation regarding using ... Closed
is related to SERVER-32494 $lookup should have option not merge ... Closed
Backwards Compatibility: Major Change
Sprint: Query 11 (03/14/16), Query 12 (04/04/16)
Participants:

 Description   

Example Order Document:

{
    _id: ObjectId("..."),
    products: [ ObjectId("..<Car ObjectId>.."), ObjectId("..<Bike ObjectId>..") ]
}

Not Working Query:

db.orders.aggregate([ {
    $lookup: {
           from: "products",
           localField: "products",    <= array of IDs
           foreignField: "_id",
           as: "productObjects"
    }
} ])

Desired Result

{
  _id: ObjectId("..."),
  products: [
    ObjectId("..<Car ObjectId>.."),
    ObjectId("..<Bike ObjectId>..")
  ],
  productObjects: [
    {<Car Object>},
    {<Bike Object>}
  ],
}

source: http://stackoverflow.com/questions/34967482/lookup-on-objectids-in-an-array



 Comments   
Comment by Ramon Fernandez Marina [ 18/Oct/18 ]

ferahl, we don't reopen tickets for shipped releases – will you please open a new SERVER ticket so we can investigate the behavior you describe? Please try to include a full reproducer.

Thanks,
Ramón.

Comment by Dominic Tobias [ 18/Oct/18 ]

Sadly this still doesn't work. Given a document in users:

{
  projects: [
    { id: ObjectId('projectId'), someOtherUserProjectSetting: true },
  ]
}

 

and query:
 

db.collection('users').aggregate([
  { $match:{ _id:ObjectId('projectId')}},
  {
    $lookup: {
      from: 'projects',
      localField: 'projects._id',
      foreignField: '_id',
      as: 'projectObjects',
    },
  },
]).toArray();

Even $unwind: $projects makes no difference, projectObjects is always null or undefined.

Comment by Xinghua Wu [ 30/Sep/16 ]

Thank you anonymous.user,
I've created a new ticket: SERVER-26404.

Comment by Kelsey Schubert [ 29/Sep/16 ]

Hi sweetwxh,

Would you please open a new ticket so we can continue to investigate this behavior?

When please include a repro script that generates and then queries some sample data so we can determine whether the output is expected. Or, alternatively, I would be happy to provide you with a link to a secure upload portal on the new ticket where you can upload your data files for us to investigate.

Thank you,
Thomas

Comment by Xinghua Wu [ 28/Sep/16 ]

I have data like:

"obj1" : [
        {
            "test" : "sdfsd", 
            "testdic" : "c0e73bf0-506b-4868-8e19-30b5d83b190e", 
            "sdf" : "389af12e-6060-4f24-b20e-4fd2cae6fcc1"
        }, 
        {
            "test" : "sdfsd", 
            "testdic" : "c0e73bf0-506b-4868-8e19-30b5d83b190e", 
            "sdf" : "389af12e-6060-4f24-b20e-4fd2cae6fcc1"
        }
    ]

my mongodb version is 3.3.14。
When I use aggregate for above data, the return field is an empty array.
Here is my script:

db.test.aggregate([{
        "$match": {
            "SerialId": "201609021703310931"
        }
    },
    {
        "$lookup": {
            "from": "DictionaryItem",
            "localField": "obj1.testdic",
            "foreignField": "_id",
            "as": "dicArray"
        }
    }
])

Comment by Charlie Swanson [ 20/Sep/16 ]

Yes, that should still work.

Comment by Yanick Rochon [X] [ 20/Sep/16 ]

Will this also work if the local key is from a subdocument inside an array?

For example :

{
  _id: 'AAAA',
  components: [
    { type: 'foo', items: [
      { itemId: 'item1', qty: 2 },
      { itemId: 'item2', qty: 1 }
    ] },
    { type: 'bar', items: [
      { itemId: 'item3', qty: 8 }
    ] }
  ]
}

db['products'].aggregate([
  { $lookup: { 
    from: 'items',
    localField: 'components.items.itemId',
    foreignField: '_id',
    as: 'componentItems'
  } }
]);

Comment by Jose Antonio Illescas Olmo [ 05/Apr/16 ]

Thank You Asya,

I downloaded (nightly) mongo and this feature works for me...

Great Work!!

Comment by Asya Kamsky [ 04/Apr/16 ]

3.3.4 is not available yet, if you want to try any 3.3.4 targeted feature once it's checked in, you can download the nightly development build from download site but it will not necessarily be stable or fully tested build.

Comment by Jose Antonio Illescas Olmo [ 04/Apr/16 ]

Can you tell me a link to download 3.3.4 (developer build) of mongodb to check this feature?

Thank you

Comment by Jose Antonio Illescas Olmo [ 29/Mar/16 ]

The order is not important for me (now)
Thank you

Comment by Charlie Swanson [ 29/Mar/16 ]

Hi joseaio,

Thank you for your request, it has been implemented and pushed to master. One thing to note about your example in the description is that the results will not come back in any guaranteed order. This is a similar problem to the one described in SERVER-7528, which might also be of interest to you. It would essentially require a custom sort order, based off the order which they were presented, which is not something we support anywhere else.

If the ordering is an important feature, I would suggest you open a separate ticket to track that work.

Comment by Githook User [ 29/Mar/16 ]

Author:

{u'username': u'cswanson310', u'name': u'Charlie Swanson', u'email': u'charlie.swanson@mongodb.com'}

Message: SERVER-22881 Expand arrays during $lookup's query.

If the value in 'localField' is an array, assume that it corresponds to
multiple entries in the foreign collection. The query to the foreign
collection will use a $in predicate, except when a regex is present, in
which case it will use a series of $eq predicates within an $or.
Branch: master
https://github.com/mongodb/mongo/commit/f23e588c180cc1a2ae79b9cbf6a4ba2a196ee215

Comment by Jose Antonio Illescas Olmo [ 12/Mar/16 ]

One comment I think this feature must be reversible

db.products.aggregate([ {
    $lookup: {
           from: "orders",
           localField: "_id", 
           foreignField: "products",     <= now the array of IDs is on foregnField
           as: "orders"
    }
} ])

And return all orders that contains the product:

{
  _id: ObjectId("..<Car ObjectId>.."),
  orders: [
    ObjectId("..<Order1 ObjectId>.."),
    ObjectId("..<Order2 ObjectId>..")
  ]
}

Generated at Thu Feb 08 04:01:41 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.