Azure Cosmos DB v2 not parsing JSON arrays

[Conlyn]

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

[upkarsingh]

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. 

[DallasBaba · Accepted solution]

@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

[Conlyn]

@DallasBaba 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

[upkarsingh]

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. 

[Conlyn]

Hi @upkarsingh, it looks like there is an updated beta version of the v2 connector out now, but we haven't tried it yet, https://learn.microsoft.com/en-us/power-query/connectors/azure-cosmos-db-v2.

We ended up utilizing a Cosmos ODBC driver on our data gateway for PowerBI.