Question 418444 in Mongodb Question posted in
The official documentation can be found here.

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",
}

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?

Tags:

2

Answers


  1. 0

    In your $lookup stage, you shouldn’t use localField and foreignField as this will perform the joining between both park and city collections and since both data types are not the same, you will not get the result in the city_park_details array.

    In the pipeline and $match stage, you need to convert _id field to String before matching the document.

    {
  from: "city",
  let: {
    parkIdStr: {
      $toString: "$parkID"
    }
  },
  pipeline: [{
    $match: {
      $expr: {
        $eq: ["$$parkIdStr", { $toString: "$_id" }]
      }
    }
  }],
  as: "city_park_details"
}
    Login or Signup to reply.
  2. 0

    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:
    local run result

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search