Mongodb $lookup when localField is Array of string and foreignField is in ObjectId formats

Question

I want to do a $lookup where the localField is a String of Array which representation of an ObjectId as string and the foreign field is an actual ObjectId.

This is order collection json

[{
  "_id": {
    "$oid": "63fc524dda1a2789fe69bf23"
  },
  "item": "almonds 1",
  "price": 12,
  "quantity": 2,
  "clientId": 3
},{
  "_id": {
    "$oid": "63fc524dda1a2789fe69bf24"
  },
  "item": "peacon 1",
  "price": 12,
  "quantity": 2,
  "clientId": 4
}]

ordeDetails collection json . which stores orderIds as ObjectId

{
  "_id": {
    "$oid": "63fc54d4da1a2789fe69bf29"
  },
  "sku": "s001",         
  "orderIds": [
    {
      "$oid": "63fc524dda1a2789fe69bf23"
    },
    {
      "$oid": "63fc524dda1a2789fe69bf24"
    }
  ]
}

When I write lookup to fetch orderDetails collection with below look up

{
  from: "orders",
  localField: "orderIds",
  foreignField: "_id",
  as: "OrderResults",
}

then then result is as expected and is working fine Result is

{
  "_id": {
    "$oid": "63fc54d4da1a2789fe69bf29"
  },
  "sku": "s001",
  "orderId": "63fc524dda1a2789fe69bf23",
  "clientIds": [
    "1",
    "2"
  ],
  "orderIds": [
    {
      "$oid": "63fc524dda1a2789fe69bf23"
    },
    {
      "$oid": "63fc524dda1a2789fe69bf24"
    }
  ],
  "OrderResults": [
    {
      "_id": {
        "$oid": "63fc524dda1a2789fe69bf23"
      },
      "item": "almonds 1",
      "price": 12,
      "quantity": 2,
      "clientId": 3
    },
    {
      "_id": {
        "$oid": "63fc524dda1a2789fe69bf24"
      },
      "item": "peacon 1",
      "price": 12,
      "quantity": 2,
      "clientId": 4
    }
  ]
}

But when my ordeDetails orderIds is array of string of ObjectId at that time this query won't work

For Example orderDetails is

{
  "_id": {
    "$oid": "63fc5542da1a2789fe69bf2e"
  },
  "sku": "s002",
  "orderId": "63fc524dda1a2789fe69bf24",
  "clientIds": [
    3,
    4
  ],
  "orderIds": [
    "63fc524dda1a2789fe69bf24",
    "63fc524dda1a2789fe69bf23"
  ]
}

My current result is

    {
  "_id": {
    "$oid": "63fc5542da1a2789fe69bf2e"
  },
  "sku": "s002",
  "orderId": "63fc524dda1a2789fe69bf24",
  "clientIds": [
    3,
    4
  ],
  "orderIds": [
    "63fc524dda1a2789fe69bf24",
    "63fc524dda1a2789fe69bf23"
  ],
  "OrderResults": []
}

Can anyone please help me where i do change my query and how i am going to achieve this ?

I already tied this solution but didn't work for me.

Mongodb $lookup when localField is string and foreignField is in ObjectId formats

Thank you in advanced!

score 0 · Accepted Answer · answered Mar 03 '23 at 06:11

Yes. Same solution provides in this link worked for me I may made some mistakes.

Here is working query

[
  {
    $lookup:
      /**
       * from: The target collection.
       * localField: The local join field.
       * foreignField: The target join field.
       * as: The name for the results.
       * pipeline: Optional pipeline to run on the foreign collection.
       * let: Optional variables to use in the pipeline field stages.
       */
      {
        from: "orders",
        let: {
          vid: "$orderIds",
        },
        pipeline: [
          {
            $match: {
              $expr: {
                $in: [
                  "$_id",
                  {
                    $map: {
                      input: "$$vid",
                      in: {
                        $toObjectId: "$$this",
                      },
                    },
                  },
                ],
              },
            },
          },
        ],
        as: "transactions",
      },
  },
]

Mongodb $lookup when localField is Array of string and foreignField is in ObjectId formats

1 Answers1