Unable to select array items from a Mongodb collection through ODBC BI connector in Power Query.

[RGouda]

Hi, everyone. I am facing issues in PowerQuery while selecting/querying array items from the https://studio3t.com/knowledge-base/articles/sql-query/#querying-arrays link. 
I have a collection below, want to select the array elements in my power query. Please let me know if I missed anything. 

Below are the errors I am getting:

Please help!

[RGouda]

@rohit_singh, your approach to load all columns and expand nested columns is taking a lot of time to load. That's the reason, I have chosen to select/query the required columns I use in the report. I want to restrict unnecessary columns in my dataset.

I am not only facing this issue for this collection but there are multiple collections as such. Considering my huge data size, I opted for this method of selecting/querying the required columns. 

[RGouda]

Thanks @rohit_singh for your quick response. I am using MongoDB ODBC driver as listed below. 

[rohit_singh]

Thanks for confirming @RGouda . In that case I would suggest a workaround by loading the table without writing an SQL statement, which means loading all columns into Power Query. You can then expand the nested columns you need manually and use them, and remove any other columns you don't need.

Kind regards,

Rohit

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[RGouda]

Thanks for your input @rohit_singh, I have tried as you suggested but it did not work.

I have tried different syntaxes for the highlighted queried column, but the result is same popping up with errors as mentioned in my post.

1. `worlimatka_result_playerCards.0.card.rank`
2. [worlimatka_result_playerCards.0.card.rank]
3. `worlimatka_result_playerCards[0.card.rank]
4. `worlimatka_result_playerCards[0].card.rank`
5. `worlimatka_result_playerCards.[0].[card].[rank]`

[rohit_singh]

Hi @RGouda ,

Sorry to hear that didn't work out for you. I'mvnot really sure why this might be happening. I was reading through the official Mongodb documentation for the BI connector and maybe this could help 
https://www.mongodb.com/docs/bi-connector/current/components/

You seem to be using the MySQL driver which is not listed for use with PowerBI. Maybe it is worthwhile trying a different driver for ODBC connection with PowerBI?

Kind regards,

Rohit

[rohit_singh]

Hi @RGouda ,

If you are using BI connector to connect to mongodb, you will first need to check the schema of the table you're trying to query, since column names might be different from the mongo document.

When you connect to odbc, please select only your odbc connection name and enter the connection string. Leave the SQL statement section blank and click on ok.

In the navigator window, you will see a list of tables. Navigate to the table you want to query (worlimatka_result) and check the column names. You can then modify your initial query according to the actual column names to query the data.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂