Home > Software design >  MongoDB aggregate $lookup with _ID's from array
MongoDB aggregate $lookup with _ID's from array

Time:06-21

I have two collections in MongoDB: "carts" and another "products"

Carts:

[{
  "_id": {
    "$oid": "62af0fefebc0b42a875c7df1"
  },
  "uuid": "6ca05ae0-522a-4db3-b380-2d2330ee1e27",
  "cartitems": [
    "62a0b24680cc2891148daf7b",
    "62a7339d91d01868921afa0a",
    "62a72f7191d01868921afa08",
    "62a7330291d01868921afa09"
  ],
  "created": "2022-06-19T14:00:47.846958537 02:00[Europe/Brussels]",
  "lastupdate": "2022-06-19T14:01:06.15165564 02:00[Europe/Brussels]"
},
{...},
{...}]

products:

[{
  "_id": {
    "$oid": "62a0b24680cc2891148daf7b"
  },
  "name": "Product1",
  "created": "2022-06-11T09:41:54.461308647 02:00[Europe/Brussels]",
  "category": "Workshops",
  "pricein": "28900",
  "lastupdate": "2022-06-17T16:09:53.385655474 02:00[Europe/Brussels]"
},
{...},
{...}]

I would like to use Aggregate:

db.carts.aggregate([
  { $match: { uuid: "6ca05ae0-522a-4db3-b380-2d2330ee1e27" } },
  {
    $lookup: {
      from: "products",
      localField: "cartitems",
      foreignField: "_id",
      as: "output"
    }
  }
])

This is not working because localField: "cartitems" should be converted:

"$addFields": {
        "convertedIdStr": {
           "$toString": "$cartitems"
        }

But I don't manage to convert because cartitems is an array. Any help would be great, Thanks a lot!

CodePudding user response:

Use $lookup with pipeline. In $lookup pipeline stage, add $match stage by filtering the (converted to string) product's _id is in cartitems (array) variable.

db.carts.aggregate([
  {
    $match: {
      uuid: "6ca05ae0-522a-4db3-b380-2d2330ee1e27"
    }
  },
  {
    $lookup: {
      from: "products",
      let: {
        cartitems: "$cartitems"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                {
                  $toString: "$_id"
                },
                "$$cartitems"
              ]
            }
          }
        }
      ],
      as: "output"
    }
  }
])

Sample Mongo Playground

  • Related