Can´t read from Azure Cosmos DB (because ObjectId?)?

[Dataura]

Hi.

I have an Azure funtion that writes data to an Azure Cosmos DB. Automatically, it creates an entry like this for any document:

{
"_id" : ObjectId("5ac205a4115db114402c7257"),

in here, all my other fields/elements like any JSON file, like "username":"Peter",

}

When in Power BI Desktop I try to read from this Azure Cosmos DB source, it gets everything wrong (not only it doesn´t recognize the "_id" and oher fields, but the columns it creates ($t, $v,_ts ....) contains strange things:

I had done this previously with a very siimilar Azure Cosmos DB source except by the OcbejctId(....) automatically added now, and it worked fine 
I can´t manage to import the data to Power BI Desktop, and I need them (I can´t get rid of the ObjectId).

Any help would be much appreciated.

Regards,

[jolea]

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.

[Anonymous]

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!

[jolea]

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.

[v-yuezhe-msft]

@Dataura,

Is there possibility that you share a JSON sample file and post expected result?

Regards,
Lydia

[Dataura]

This is a document in the Azure Cosmos DB:

{
"_id" : ObjectId("5ac205a2341db114402c7258"),
"client_id" : "Marc",
"userName" : "51554",
"easyName" : "Marcas",
"appliance_id" : 4,
"serviceLocationId" : 25002,
"timestamp" : 1522664868326,
"datetime" : "4/2/2018 10:27:48 AM",
"appliance_type" : "Case element",
"appliance_name" : "",
"human_interaction" : 3,
"has_reactive" : 3,
"has_multiple_power" : 3
}

The "_id" : ObjectId("5ac205a2341db114402c7258") is automatically generated by Azure.

Power BI Desktop can´t read it.