How to read cosmosdb mongodb API objectId field from a synapse analytical collection link

Question

I am linking Synapse serverless sql pool with Cosmosdb with Mongodb API. For background, MongoDb sets its _id and reference ids to objectId(). To do a SQL join on this field, we need to convert the value to string. This is how the data looks:

{"objectId":"0x676487C9C43115490D49A040"}

We can notice the hexadecimal value. To read this, I am trying to do the following:

• Notice the schema definition to VARBINARY and then a convert to a VARCHAR

SELECT CONVERT(VARCHAR(50), statusId) as statusId, 
FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                 CONNECTION = '***',
                 OBJECT = 'my=collection',
                 SERVER_CREDENTIAL = 'cosmosdb_creds'
 )
WITH (
     statusId VARBINARY(50) '$.statusId.objectId',
)
AS [log]

The selected value is a binary

‰ÃÔåöx¡²ÃÔå

I am wondering if this a coalesce setting problem with my database or is there another approach to read the objectId values?

Thanks,

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

When dealing with ObjectId in CosmosDB using the MongoDB API, it's common to encounter this challenge due to the binary representation of the ObjectId. Here's a detailed explanation and solution:

The Problem

ObjectId values in MongoDB are 12-byte binary values. When reading this data through Synapse Serverless SQL Pool, these values are returned as VARBINARY, and directly converting them to VARCHAR can result in unexpected output, as you're observing.

Solution

To properly convert and work with the ObjectId, you need to:

1. Extract the VARBINARY data: Retrieve the raw binary data using the schema definition in the WITH clause.
2. Convert VARBINARY to a readable hexadecimal string: Use the CONVERT function with the VARCHAR format and prepend a 0x to represent the hexadecimal value.
3. Handle potential encoding issues: Ensure the conversion aligns with the expected hexadecimal representation.

Try this updated query:

SELECT 
    CONCAT('0x', CONVERT(VARCHAR(50), statusId, 1)) AS statusId
FROM OPENROWSET(
    PROVIDER = 'CosmosDB',
    CONNECTION = '***',
    OBJECT = 'my_collection',
    SERVER_CREDENTIAL = 'cosmosdb_creds'
)
WITH (
    statusId VARBINARY(12) '$.statusId.objectId'
) AS [log];

1. Schema Definition:
   • Use VARBINARY(12) as ObjectId is always 12 bytes.
2. Conversion to Hexadecimal:
   • CONVERT(VARCHAR(50), statusId, 1) converts the binary data to a hexadecimal string.
   • CONCAT('0x', ...) prefixes the result with 0x for readability and alignment with MongoDB's hexadecimal representation.
3. Result:
   • The query will return the ObjectId as a string, e.g., 0x676487C9C43115490D49A040.

Additional Considerations

Joining with Other Tables: Ensure the target table’s ObjectId values are stored or converted into the same string format (e.g., prefixed with 0x).

Performance: Converting VARBINARY to VARCHAR for large datasets can be compute-intensive. Test performance to ensure this approach meets your requirements.

Validation: Double-check the extracted ObjectId values with a sample from your MongoDB collection to confirm accuracy.

This approach ensures you can seamlessly handle and join ObjectId values in Synapse Serverless SQL Pool.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.