Cosmos DB V2 Connector doesn't load json arrays

[sittell]

Hi, 

I'm trying to build Power BI reports on top of Cosmos DB using Cosmos DB V2 Connector and a Direct Query Mode. Currently I have an issue with json data which contains arrays. In this case when loading the data, arrays are shown as a separate table which has been evaluating and never being loaded. All other data has been loaded sucessfully, but has a column with a link to the array as a table which doesn't work. Has anyone had the same problem and has an idea how it could be solved? Is there any way to work with arrays with Cosmos DB V2 Connector for Power BI or you have to create some data preparation in between?

I've read this topic, but it doesn't really suggest any solution.

I would be grateful for any feedback!

Best regards,

Lisa

[ananco]

Hi!

Did you find the solution? I'm stuck with this problem and apparently nobody has come out with the solution jet. 

Kind regards

[upkarsingh]

Hi @ananco, I was wondering if you got any leads on this one. I am experiencing the same situation. Thanks!

[AMaksymyshyn]

Hi, have the same issue with Cosmos DB V2 Connector,
Example document:

{
    "id": "test",
    "metrics": [
       3, 5, 6
    ]
}

Expression.Error: The column 'id' of the table wasn't found.
Details:
id

[upkarsingh]

Hi @AMaksymyshyn, I am in the same situtation and was wondering if you have any leads in regards to the same query you mentioned above. 

[v-yangliu-msft]

Hi  @sittell ,

You can check the following official documentation for known issues you may be experiencing with the Cosmos DB V2 connector

Troubleshoot connector and format issues in mapping data flows - Azure Data Factory | Microsoft Lear...

Or you can consider transforming your data in Cosmos DB to flatten the arrays.

CosmosDB, help flatten and filter by nested array - Stack Overflow

Flatten nested arrays in cosmosdb sql - Stack Overflow

This is the related document, you can view this content:

Solved: Re: Removing any columns causes errors - Microsoft Fabric Community

Solved: Expression.Error: The column 'Column1' of the tabl... - Microsoft Fabric Community

Solved: Re: The Column '___' of the table wasn't found. - Power Platform Community (microsoft.com)

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sittell]

Hi Liu, 

Thank you for your feedback! Is there any way to specify an sql query for Cosmos DB V2 connector in order to flatten Cosmos DB data?

Thanks.