1

How to do $lookup on a field that is an array of ids(long) rather than just a single id? Trying to retrieve product information from collection 'products' by _id, put it in an array, then embed product information list to warehouse's document.

Unfortunately, our mongo database is on 3.2, thus new feature allows lookup with array(https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#use-lookup-with-an-array) doesn't apply.

Looked through a bunch of solutions online, this seems to be closest, https://stackoverflow.com/a/50207150, modified this to the following:

db.getCollection('warehouses').aggregate([

   {"$match": 
        // conditions to be matched
   },
    { $lookup:
       {
         from: 'products',
         let: {'productIds' : '$productIds' },
         pipeline: [
           { $match: { $expr: {$in: ["$_id", "$$productIds"] } } },
         ],
         as: 'productLists'
       }
     }
])

However, it's getting the following error:

"arguments to $lookup must be strings, let: .... is type 3".

_id is number(long) not string, is there any workaround for this? Thanks.

Sample warehouse document:

{
    "_id" : NumberLong(1),
    productIds: [NumberLong(1), NumberLong(2), NumberLong(3)],
    "warehouseProperty1" : "warehouseProperty1",
    "warehouseProperty2" : "warehouseProperty2",
    "warehouseProperty3" : "warehouseProperty3",
    "warehouseProperty4" : "warehouseProperty4"
}

Sample product document:

{
    "_id" : NumberLong(1),
    "productProperty1" : "productProperty1",
    "productProperty2" : "productProperty2",
    "productProperty3" : "productProperty3",
    "productProperty4" : "productProperty4"
}

Desired output:

{
    "_id" : NumberLong(1),
    productIds: [
        {
            "_id" : NumberLong(1),
            "productProperty1" : "productProperty1",
            "productProperty2" : "productProperty2",
            "productProperty3" : "productProperty3",
            "productProperty4" : "productProperty4"
        },
        {
            "_id" : NumberLong(2),
            "productProperty1" : "productProperty1",
            "productProperty2" : "productProperty2",
            "productProperty3" : "productProperty3",
            "productProperty4" : "productProperty4"
        },
        {
            "_id" : NumberLong(3),
            "productProperty1" : "productProperty1",
            "productProperty2" : "productProperty2",
            "productProperty3" : "productProperty3",
            "productProperty4" : "productProperty4"
        },
    ],
    "warehouseProperty1" : "warehouseProperty1",
    "warehouseProperty2" : "warehouseProperty2",
    "warehouseProperty3" : "warehouseProperty3",
    "warehouseProperty4" : "warehouseProperty4"
}
Kennard
  • 845
  • 2
  • 12
  • 32
  • 1
    `let` is not a key that exists in 3.2 either. https://docs.mongodb.com/v3.2/reference/operator/aggregation/lookup/ – D. SM Jul 13 '20 at 00:17
  • https://docs.mongodb.com/v3.2/reference/operator/aggregation/lookup/#use-lookup-with-an-array says to use $unwind. – D. SM Jul 13 '20 at 00:18

0 Answers0