2

I want to get the subdocuments from another collection using $lookup but it doesn't work. Currently brain dead...

I have a collection for Transactions

example transaction

{
  type: 'PURCHASE',  // but it can be something else also eg ORDER
  reference: '11', // String
  amount: 50,
  date: 2018-07-18T10:00:00.000Z
}

I have a collection for Purchases

{
  code: 11 // Integer
  name: 'Product X',
  amount: 50
}

My aggregation is the following

Purchase.aggregate([ 
        {
            $lookup:
              {
                from: "transactions",   
                let: { code: '$code' },
                pipeline: [ 
                    {

                    },
                    { 
                        $match: { $expr:
                            { $and:
                               [
                                 { $eq: [ "$reference",  "$$code" ] },
                                 { $eq: [ "$type", "PURCHASE" ] }
                               ]
                            }
                         }

                    }
                ], 
                as: "transactions",

              }
         }
 ]);

The result is an empty tarnsactions array...

webmaster
  • 1,960
  • 24
  • 29
  • 1
    String != Integer. In 4.0 you can add a conversion before lookup stage. Something like `let: { code:{$toString:'$code'} }`. For older versions you are required to have same type. – s7vr Aug 14 '18 at 22:22

1 Answers1

2

You can try below aggregation in mongodb 3.6. Just change code type from integer to string using $toLower aggregation or can use $toString in mongodb 4.0

Purchase.aggregate([ 
  { "$lookup": {
    "from": "transactions",   
    "let": { "code": { "$toLower": "$code" } },
    "pipeline": [ 
      { "$match": {
        "$expr": { "$eq": [ "$reference",  "$$code" ] },
        "type": "PURCHASE"
      }}
    ],
    "as": "transactions"
  }}
])
Ashh
  • 44,693
  • 14
  • 105
  • 132
  • Thank you very much, I just changed, let: { code: '$code' }, to "let": { "code": { "$toLower": "$code" } } and it worked ;) – webmaster Aug 15 '18 at 09:23