Forum Discussion
Can´t read from Azure Cosmos DB (because ObjectId?)?
I have also hit my head on this issue using the Azure CosmosDb MongoDb API, finally found a workaround which so far is working...
Add a custom column with the following:
Binary.ToText(Binary.FromList(List.Alternate(Binary.ToList(Binary.From(Text.ToBinary([#"Document.$v._id.$v"], TextEncoding.BigEndianUnicode))), 1, 1, 0)),1)
This will translate field: Document.$v._id.$v to a string representation of the ObjectId.
I've gone code blind looking at this, but best I can explain is the text encoding on the "_id" field isn't handled gracefully yet on the connector (which is still in beta). So have to translate to binary specifying correct encoding, parse it, and translate it back to a hexidecimal text value.
Just beware, handling on the ObjectId type might be handled differently when the connector moves out of Beta and as more enhancements come to Azure Cosmos Db, but this should get you unblocked for now.
- Anonymous7 years agoNot applicable
Hi Jolea,
I am facing same issue. tring to use your solution but didnt understand how to use it. is this for .NET SDK?
Binary.ToText(Binary.FromList(List.Alternate(Binary.ToL
I am using Javascript ( NodeJS) SDK. can you put some more detail about how to add Custom column?
I am using package https://www.npmjs.com/package/@azure/cosmos
and tring to make working sample code
// JavaScript const cosmos = require("@azure/cosmos"); const CosmosClient = cosmos.CosmosClient; const endpoint = "[hostendpoint]"; // Add your endpoint const masterKey = "[database account masterkey]"; // Add the masterkey of the endpoint const client = new CosmosClient({ endpoint, auth: { masterKey } }); const databaseDefinition = { id: "sample database" }; const collectionDefinition = { id: "sample collection" }; const documentDefinition = { id: "hello world doc", content: "Hello World!" }; async function helloCosmos() { const { database: db } = await client.databases.create(databaseDefinition); console.log("created db"); const { container } = await db.containers.create(collectionDefinition); console.log("created collection"); const { body } = await container.items.create(documentDefinition); console.log("Created item with content: ", body.content); await db.delete(); console.log("Deleted database"); } helloCosmos().catch(err => { console.error(err); });Thank you for help!
- jolea6 years ago
Microsoft Employee
Apologies for delayed reply, it looks like the landscape has changed but in case this helps anyone else.
My solution was for Power BI, but the root cause seemed to be using a SQL API protocol to talk with a Mongo API version of Azure Cosmos Db. The id field in the SQL API is a string, and in the Mongo API id was type ObjectId.
Similarly, I suspect Anonymous had encountered this issue by using a cosmos library to a Mongo API instance of Azure Cosmos Db. We migrated our solution to SQL API a couple years back as the local development tools became stronger and available, so I don't have good examples to test on anymore.
Hope this helps in case anyone encounters similar issues.