Forum Discussion
Advocate IAzure Cosmos DB v2 not parsing JSON arrays
I am trying to use the new Azure Cosmos DB v2 connector in Direct Query mode. At the table level it does not seem able to properly recognize JSON array structs "[]". When viewing the tables in Power Query Editor it labels an array column as two columns, one labeled "table_column[](id)" and another as "table_column[](Partition Key)" showing the value as "Table". If I go to Expand the column it shows that No Columns were found. Columns that are JSON object types "{}" are correctly expanded as "column_subcolumn" When using the v1 connector it properly shows those array columns as a "List" type value while the object columns are a "Record" type.
As I need to use this in Direct Query, is there a way to get the V2 connector to properly allow access to the nested array structs?
-Conlyn
Conlyn this is a known issue with the connector, and there is a workaround to manually update the script of the Azure Cosmos DB/JSON source to get the map data type support. Kindly read this Microsoft article :
https://learn.microsoft.com/en-us/azure/data-factory/data-flow-troubleshoot-connector-format
More also ensure that you are using the latest version
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!Let me know if this work
@ me in replies or I'll lose your thread!!!Thanks
5 Replies
- DallasBaba
Skilled Sharer
Conlyn this is a known issue with the connector, and there is a workaround to manually update the script of the Azure Cosmos DB/JSON source to get the map data type support. Kindly read this Microsoft article :
https://learn.microsoft.com/en-us/azure/data-factory/data-flow-troubleshoot-connector-format
More also ensure that you are using the latest version
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!Let me know if this work
@ me in replies or I'll lose your thread!!!Thanks
ConlynDallasBaba The solution in that MS article is for updating a script in Data Factory (DSL), and not for the PowerBI Power Query M language. I'm not seeing how I can define a schema from the results that come back when defining the source from CosmosDB.Contents(xyz) in Power Query.
-Conlyn
Hey Conlyn, I am in the same situtation and was wondering if you have any leads in regards to the same query you mentioned above.
Power BI tutorial for Azure Cosmos DB | Microsoft Learn
It seems like a known limitation for using v2 version.The V2 connector doesn't support complex data types such as arrays, objects, and hierarchical structures. We recommend the [Fabric Mirroring for Azure Cosmos DB](/articles/cosmos-db/analytics-and-business-intelligence-overview.md feature for those scenarios.
You can use fabric mirroring however the only supported auth method is Key based.
