Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pipeline in lookup aggregation not working in mongodb

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 ?)

like image 996
KitKatKot Avatar asked Jul 03 '18 14:07

KitKatKot


People also ask

What passes through a MongoDB aggregation pipeline?

An aggregation pipeline consists of one or more stages that process documents: Each stage performs an operation on the input documents. For example, a stage can filter documents, group documents, and calculate values. The documents that are output from a stage are passed to the next stage.

What are lookup in aggregation for MongoDB?

The $lookup operator is an aggregation operator or an aggregation stage, which is used to join a document from one collection to a document of another collection of the same database based on some queries. Both the collections should belong to the same databases.

How aggregation works in MongoDB?

In MongoDB, aggregation operations process the data records/documents and return computed results. It collects values from various documents and groups them together and then performs different types of operations on that grouped data like sum, average, minimum, maximum, etc to return a computed result.

How does lookup work in MongoDB?

The MongoDB Lookup operator, by definition, “Performs a left outer join to an unshared collection in the same database to filter in documents from the “joined” collection for processing.” Simply put, using the MongoDB Lookup operator makes it possible to merge data from the document you are running a query on and the ...


1 Answers

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"
  }}
])
like image 164
Ashh Avatar answered Sep 22 '22 02:09

Ashh