ODBC MONGO TROUBLES ON SQL QUERY

[Anonymous]

Hello! im trying to connect pbi with mi db on mongo atlas  i have set up bi connector and the odbc in mi local pc

I can see the tables when i get the data , but the problems come when i do the connection to odbc it shows me several collections(really in my database is only 1 collection named orders) one who is the real collection and other tables wich are the nested collections inside the principal as you see :

if i choose to import the orders table only , and then i expand te column items the query gets real real slow and never bring results ( i guess its because im getting in a circular reference)

Ive tried to filter the table just to bring some fields directly with a sql statement  , but get me an error as you see:

but when i connect to the entire table the column exists

Im sure that the error is somewere in my sql but ive never used the bi connector of mongo db atlas 

Any help is welcome , and if you need furter information just let me know 

Regards!!

[Anonymous]

Hi, @gregrosa85, @Anonymous, @dax: I am facing issues in Power Query while selecting/querying array items from 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.

Below are the errors I am getting:

Please help!. 

[gregrosa85]

Hello,

in my case ` (backticks) helped. I needed to wrap it like `nestedTableName.field` with backticks.

In my case BetconstructTrans is a base table and jsonBetConstAddInfo is an array with nested docments 

[dax]

Hi @Anonymous , 

I saw your column name is "order_items.item.id" in your image, so you could try to use this in your query to see whether it work or not(you also need to make sure your table name is correct). By the way, I am not sure whether this is caused by the "." in column name, if above column name can't work, you ciould try to rename column as "order_items_item_id", then use this in query to check it.

Best Regards,
Zoe Zhi

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