CODEWITHSUNDEEP
mongodbaggregation-framework
bendataclear
bendataclear

Reputation: 3848

MongoDB Aggregation - Lookup pipeline not returning any documents

I'm having hard time getting $lookup with a pipeline to work in MongoDB Compass.

I have the following collections:

Toys

First Image - Toys

Data

[
  {
    "_id": {
      "$oid": "5d233c3bb173a546386c59bb"
    },
    "type": "multiple",
    "tags": [
      ""
    ],
    "searchFields": [
      "Jungle Stampers - Two",
      ""
    ],
    "items": [
      {
        "$oid": "5d233c3cb173a546386c59bd"
      },
      {
        "$oid": "5d233c3cb173a546386c59be"
      },
      {
        "$oid": "5d233c3cb173a546386c59bf"
      },
      {
        "$oid": "5d233c3cb173a546386c59c0"
      },
      {
        "$oid": "5d233c3cb173a546386c59c1"
      },
      {
        "$oid": "5d233c3cb173a546386c59c2"
      },
      {
        "$oid": "5d233c3cb173a546386c59c3"
      },
      {
        "$oid": "5d233c3cb173a546386c59c4"
      }
    ],
    "name": "Jungle Stampers - Two",
    "description": "",
    "status": "active",
    "category": {
      "$oid": "5cfe727cac920000086b880e"
    },
    "subCategory": "Stamp Sets",
    "make": "",
    "defaultCharge": null,
    "defaultOverdue": null,
    "sizeCategory": {
      "$oid": "5d0cfde57561e107c88fbde3"
    },
    "ageFrom": {
      "$numberInt": "24"
    },
    "ageTo": {
      "$numberInt": "120"
    },
    "images": [
      {
        "_id": {
          "$oid": "5d233c3bb173a546386c59bc"
        },
        "id": {
          "$oid": "5d233c39b173a546386c59ba"
        },
        "url": "/toyimages/5d233c39b173a546386c59ba.jpg",
        "thumbUrl": "/toyimages/thumbs/tn_5d233c39b173a546386c59ba.jpg"
      }
    ],
    "__v": {
      "$numberInt": "2"
    }
  }
]

Loans

Second Image - Loans

Data

[
  {
    "_id": {
      "$oid": "5e1f1661b712215978c746d9"
    },
    "tags": [],
    "member": {
      "$oid": "5e17495e4f81ab3f900dbb63"
    },
    "source": "admin portal - [email protected]",
    "items": [
      {
        "id": {
          "$oid": "5e1f160eb712215978c746d5"
        },
        "status": "new",
        "_id": {
          "$oid": "5e1f1661b712215978c746db"
        },
        "toy": {
          "$oid": "5d233c3bb173a546386c59bb"
        },
        "cost": {
          "$numberInt": "0"
        }
      },
      {
        "id": {
          "$oid": "5e1f160eb712215978c746d5"
        },
        "status": "new",
        "_id": {
          "$oid": "5e1f1661b712215978c746da"
        },
        "toy": {
          "$oid": "5d233b1ab173a546386c59b5"
        },
        "cost": {
          "$numberInt": "0"
        }
      }
    ],
    "dateEntered": {
      "$date": {
        "$numberLong": "1579095632870"
      }
    },
    "dateDue": {
      "$date": {
        "$numberLong": "1579651200000"
      }
    },
    "__v": {
      "$numberInt": "0"
    }
  }
]

I am trying to return a list of toys and their associated loans that have a status of 'new' or 'out'.

I can use the following $lookup aggregate to fetch all loans:

{
  from: 'loans',
  localField: '_id',
  foreignField: 'items.toy',
  as: 'loansSimple'
}

However I am trying to use a pipeline to load loans that have the two statuses I am interested in, but it always only returns zero documents:

{
  from: 'loans',
  let: {
    'toyid': '$_id'
  },
  pipeline: [
    {
      $match: {
        $expr: {
          $and: [
            {$eq: ['$items.toy', '$$toyid']},
            {$eq: ['$items.status', 'new']} // changed from $in to $eq for simplicity
          ]
        }
      }
    }  
  ],
  as: 'loans'
}

This always seems to return 0 documents, however I arrange it:

Image Three - Results

Have I made a mistake somewhere?

I'm using MongoDB Atlas, v4.2.2, MongoDB Compass v 1.20.4

Upvotes: 0

Views: 1426

Answers (2)

Valijon
Valijon

Reputation: 13113

You are trying to search $$toyid inside inner array, but Operator Expression $eq cannot resolve it.

Best solution: $let (returns filtered loans by criteria) + $filter (applies filter for inner array) operator helps us to get desired result.

db.toys.aggregate([
  {
    $lookup: {
      from: "loans",
      let: {
        "toyid": "$_id",
        "toystatus": "new"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $gt: [
                {
                  $size: {
                    $let: {
                      vars: {
                        item: {
                          $filter: {
                            input: "$items",
                            as: "tmp",
                            cond: {
                              $and: [
                                {
                                  $eq: [
                                    "$$tmp.toy",
                                    "$$toyid"
                                  ]
                                },
                                {
                                  $eq: [
                                    "$$tmp.status",
                                    "$$toystatus"
                                  ]
                                }
                              ]
                            }
                          }
                        }
                      },
                      in: "$$item"
                    }
                  }
                },
                0
              ]
            }
          }
        }
      ],
      as: "loans"
    }
  }
])

MongoPlayground

Alternative solution 1. Use $unwind to flatten items attribute. (We create extra field named tmp which stores items value, flatten it with $unwind operator, match as you were doing and then exclude from result)

db.toys.aggregate([
  {
    $lookup: {
      from: "loans",
      let: {
        "toyid": "$_id"
      },
      pipeline: [
        {
          $addFields: {
            tmp: "$items"
          }
        },
        {
          $unwind: "$tmp"
        },
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$tmp.toy",
                    "$$toyid"
                  ]
                },
                {
                  $eq: [
                    "$tmp.status",
                    "new"
                  ]
                }
              ]
            }
          }
        },
        {
          $project: {
            tmp: 0
          }
        }
      ],
      as: "loans"
    }
  }
])

MongoPlayground

Alternative solution 2. We use $reduce to create toy's array and with $in operator we check if toyid exists inside this array.

db.toys.aggregate([
  {
    $lookup: {
      from: "loans",
      let: {
        "toyid": "$_id"
      },
      pipeline: [
        {
          $addFields: {
            toys: {
              $reduce: {
                input: "$items",
                initialValue: [],
                in: {
                  $concatArrays: [
                    "$$value",
                    [
                      "$$this.toy"
                    ]
                  ]
                }
              }
            }
          }
        },
        {
          $match: {
            $expr: {
              $in: [
                "$$toyid",
                "$toys"
              ]
            }
          }
        },
        {
          $project: {
            toys: 0
          }
        }
      ],
      as: "loans"
    }
  }
])

Upvotes: 1

Tom Slabbaert
Tom Slabbaert

Reputation: 22316

$expr receives aggregation expressions, At that point $$items.toy is parsed for each element in an array as you would expect (however if it would it will still give you "bad" results as you'll get loans that have the required toy id and any other item with status new in their items array).

So you have two options to work around this:

  1. If you don't care about the other items in the lookup'd document you can add an $unwind stage at the start of the lookup pipeline like so:
{
    from: 'loans',
    let: {
        'toyid': '$_id'
    },
    pipeline: [
        {
            $unwind: "$items"
        },
        {
            $match: {
                $expr: {
                    $and: [
                        {$eq: ['$items.toy', '$$toyid']},
                        {$eq: ['$items.status', 'new']} // changed from $in to $eq for simplicity
                    ]
                }
            }
        }
    ],
    as: 'loans'
}
  1. If you do care about them just iterate the array in one of the possible ways to get a 'correct' match, here is an example using $filter
    {
        from: 'loads',
        let: {
            'toyid': '$_id'
        },
        pipeline: [
            {
                $addFields: {
                    temp: {
                        $filter: {
                            input: "$items",
                            as: "item",
                            cond: {
                                $and: [
                                    {$eq: ["$$item.toy", "$$toyid"]},
                                    {$eq: ["$$item.status", "new"]}
                                ]
                            }
                        }


                    }
                }

            }, {$match: {"temp.0": {exists: true}}}
        ],
        as: 'loans'
    }

Upvotes: 1

Related Questions