Hi,
I'm using metabase and new in mongoDB. I've connected my database to metabase, and now I need to join 2 documents of mongoDB in metabase. The exact query that I've written in datagrip has data, but the same code in metabase faces bugs.
I've tried many things and did many search but I couldn't find any solution.
Could you please give me a sample which works on metabase?
thanks a lot
Any idea?!
Hi @ghazalak
Post "Diagnostic Info" from Admin > Troubleshooting.
It would probably be helpful if you posted your query.
What does "faces bugs" mean? Errors? If yes, then post the error.
Metabase does not support joins:
https://github.com/metabase/metabase/issues/9095 - upvote by clicking 
on the first post
With that being said, it's very inefficient to do joins on Mongo:
https://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb
I've already upvoted that issue.
I've got this error:
readStartArray can only be called when CurrentBSONType is ARRAY, not when CurrentBSONType is DOCUMENT.
on this code:
{
$lookup:
{
from: "plans",
localField: "$plan",
foreignField: "$id",
as: "result"
}
}
I also tried this one:
Object_storage.plans.aggregate([
{
$lookup:
{
from: "plans",
localField: :"$plan",
foreignField: "$id",
as: "result"
}
}
])
error:
JSON reader was expecting a value but found 'Object_storage'.
@ghazalak I guess it's something to do with your field types, as noted by the error returned (which comes from Mongo).
This works fine, when I run it on our Sample Dataset's Order table:
[{
$lookup:
{
from: "products",
localField: "product_id",
foreignField: "id",
as: "result"
}
}]
Thank you so much, I should replace '{' with '[' at the beginning.
Hi @flamber, Lookup syntax worked for me. I have another query regarding the same, if i want to fetch only one column from the lookup collection, how can i do that , Below is my code , in this code "Orderitems" is a lookup collection and wanted to fetch only productSource field from orderitems collection. the mentioned query is running but not populating the column. Please assist :- [
{
"$match": {
"$or": [
{
"orderStatus": "COMPLETED"
},
{
"orderStatus": "PROCESSED"
},
{
"orderStatus": "SHIPPED"
}
]
}
},
[[ {
$match : {{Store_Name}}
}, ]]
[[ {
$match : {{raena_username}}
}, ]]
[[ {
$match : {{createdAt}}
}, ]]
{
"$lookup":
{
from: "orderitems",
localField: "metaData.item_list.item_sku",
foreignField: "sku",
as: "items",
}
}, { "$unwind": "$items" },
{ $project: { 'items.productSource': 0 } },
{
"$group": {
"_id": {
"Sku_Name": "$metaData.item_list.item_sku"
},
items : {
$push: 'items'
},
"count": {
"$addToSet": "$marketPlaceOrderId"
},
"Unique_Seller_count": {
"$addToSet": "$metaData.event.store.account.raena_user_phone_no"
},
"sum": {
"$sum": "$totalAmount"
}
}
},
{
"$sort": {
"_id": 1
}
},
{
"$project": {
"_id": false,
"Sku_Name": "$_id.Sku_Name",
"items" : "$_id.items",
"count": {
"$size": "$count"
},
"Unique_Seller_count": {
"$size": "$Unique_Seller_count"
},
"sum": true
}
},
{
"$sort": {
"Sku_Name": 1
}
}
]
@oshimmahajan Have you tried searching the internet for mongodb views ?
https://www.mongodb.com/docs/manual/core/views/
I don't know what you are trying to do, but remove all Metabase variables, so you just have plain Mongo native query and then try getting help with those queries in a forum dedicated to Mongo.
Yes @flamber i did , but it wasn't working as per the requirement, Below is the code which i am using for creating view and it seems it has created a view but giving output as something else :-
db.createView ( "order_orderitems" , "orders" , [
{
$lookup :
{
from : "orderitems" ,
localField : "metaData.item_list.item_sku" ,
foreignField : "sku" ,
as : "items"
}
},
{
$project :
{
_id : 0 ,
item_sku : 1 ,
marketPlaceOrderId : 1 ,
raena_user_phone_no : 1 ,
totalAmount : 1 ,
Productsource : "$items.Productsource"
}
},
{ $unwind : "$Productsource" }
] )
@oshimmahajan try getting help with those queries in a forum dedicated to Mongo or stackoverflow.com