MongoDB lookup join between string and UUID

I am trying to use mongo lookup operator to join data between two collections.

I have a collection named parks looking

{
  "_id": {
    "$binary": {
      "base64": "5y1dpNaZSEOTJiace1Ta2A==",
      "subType": "04"
    }
  },
  "parkID": "9c98c8ee-17a6-4db7-8115-c55fa470480d", //parkID = city._id, parkid is same as the cityid
}

I have another collection named city

{
  "_id": {
    "$binary": {
      "base64": "nJjI7hemTbeBFcVfpHBIDQ==",
      "subType": "04"
    }
  },
  "name": "Bellaire",
  }

My lookup aggregation operation on parks collection looks like

[
  {
    $match: {
      isDeleted: false,
      _id: {
        $eq: UUID(
          "e72d5da4-d699-4843-9326-269c7b54dad8"
        )
      }
    }
  },
  {
    $lookup: {
      from: "city",
      localField: "parkID",
      let: { parkIdStr: { $toString: "$parkID" } },
      foreignField: "_id",
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: ["$$parkIdStr", "$_id"]
            }
          }
        }
      ],
      as: "city_park_details"
    }
  }
]

The city_park_details is an empty array despite valid data

This is happening because park.parkID is a string whereas the city._id is a UUID

How do I successfully lookup?

You are probably using a MongoDB with a version prior to v8.0. That's why the $toUUID and relevant $convert is not yet supported.

There are 2 options for you:

1. upgrade to MongoDB v8.0+ so you can use $toUUID
2. use $function to parse the UUID into a string and perform the $lookup

1. upgrade to MongoDB v8.0+ so you can use $toUUID

db.parks.aggregate([
  {
    "$lookup": {
      "from": "city",
      "let": {
        "parkIdStr": {
          "$toUUID": "$parkID"
        }
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$eq": [
                "$$parkIdStr",
                "$_id"
              ]
            }
          }
        }
      ],
      "as": "city_park_details"
    }
  }
])

Here is the result of my local run:

2. use $function to parse the UUID into a string and perform the $lookup.

NOTE: $function may decrease performance per official doc. It is also deprecated since MongoDB v8.0

db.parks.aggregate([
  {
    "$lookup": {
      "from": "city",
      "let": {
        "parkID": "$parkID"
      },
      "pipeline": [
        {
          "$set": {
            "uuid": {
              "$function": {
                "body": "function(id) {return id.hex()}",
                "args": [
                  "$_id"
                ],
                "lang": "js"
              }
            }
          }
        },
        {
          "$set": {
            "uuid": {
              "$concat": [
                {
                  "$substrCP": [
                    "$uuid",
                    0,
                    8
                  ]
                },
                "-",
                {
                  "$substrCP": [
                    "$uuid",
                    8,
                    4
                  ]
                },
                "-",
                {
                  "$substrCP": [
                    "$uuid",
                    12,
                    4
                  ]
                },
                "-",
                {
                  "$substrCP": [
                    "$uuid",
                    16,
                    4
                  ]
                },
                "-",
                {
                  "$substrCP": [
                    "$uuid",
                    20,
                    12
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "city_park_details"
    }
  }
])

Mongo Playground