Pipeline in lookup aggregation not working in mongodb

Question

I am new to mongodb so I hope this does not come-off as a very elementary question. I've done some research and tried to apply what I've found but something just seems to escape me.

I have two collections of the following format:

-----------------------------------------------------------------------
Shop
-----------------------------------------------------------------------
{
    "shopId": "1002",
    "shopPosId": "10002",
    "description": "some description"
}

-----------------------------------------------------------------------
Compte
-----------------------------------------------------------------------
{
    "shopId": "9000",
    "shopPosId": "0000",
    "clientUid": "474192"
}

I want to join those and before doing so, I want to filter out the Shops which do not have the shopPosId field.

Here's my code:

Compte.aggregate([
    {
        $match:
            { 
                $and:[{"clientUid":clientUid}]
            }
    },
    {      
        $lookup:
        {
            from: "Shop",
            localField: "shopId",
            foreignField: "shopId",
            let: {"Shop.shopPosId": "$shopPosId"},
            pipeline: [{$match: {"shopPosId": {"$exists": false}}}],
            as: "shopDescr"
        }
        }]
);

the returned result is an undefined, which means the query doesn't make much sense (because in fact I should at least get a void array).

Is this because the two collections have the shopPosId field? (if so, isn't this line let: {"Shop.shopPosId": "$shopPosId"} supposed to take care of it ?)

Solution

As Alex commented you are mixing both the $lookup syntax here... So the correct will be

Compte.aggregate([
  { "$match": { "$and": [{ "clientUid": clientUid }] }},
  { "$lookup": {
    "from": "Shop",
    "let": { "shopId": "$shopId" },
    "pipeline": [
      { "$match": {
        "$expr": { "$eq": [ "$shopId", "$$shopId" ] },
        "shopPosId": { "$exists": false }
      }}
    ],
    "as": "shopDescr"
  }}
])

Comments

[Alex Blex]

You are mixing $lookup syntax. It is either from-localField-foreignField-as or from-let-pipeline-as, but not both together.

[KitKatKot]

Ok, this is informative, thanks! Still, I get an undefined result, I'd assume it's to do with my data except I really don't think so. Is it necessary to have mongoDB 3.6 to be able and apply a pipeline in a lookup ?

[KitKatKot]

Yeah, seems like it is, confirmed in the officiel documentation: docs.mongodb.com/manual/reference/operator/aggregation/looku‌​p/…