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

[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