1

Need mongodb for multi-table connections. Redash will support the $lookup operation.

2

Redash supports the $lookup aggregation. Suppose you have two collections called movies and comments, you could join the collections like this:

{
    "collection": "movies",
    "aggregate": [
        {"$lookup": {
            "from": "comments",
            "localField": "_id",
            "foreignField": "movie_id",
            "as": "myJoin"
        }},
        {"$limit": 10}
        ]
}

The usual caveats apply: if your data isn’t normalized you may need to include an $unwind in your pipeline. Also, be careful about projecting large relationships without a $limit aggregation. When I wrote this example I accidentally tried to pull 100 comments per movie (and there are 50k movies in my sample collection). The browser wasn’t happy with me ;-).

3

I’m new to Redash and trying to visualize from MongoDB. Most of my query works but I’m struggling with the join even after using your query.

I’m trying to join a collection “final_report_data” with another collection “hil_tagged_data” using the “session_id” field and then display the “tagger_id” field from the “hil_tagged_data” table. The below query has something missing. Can you plz help

{
    "collection": "final_report_data",
    "aggregate": [
        {
            "$lookup": {
                "from": "hil_tagged_data",
                "localField": "session_id",
                "foreignField": "session_id",
                "as": "myJoin"
            }
        },
        { "$unwind": "$myJoin" }
    ],
    
    "fields": {
        "_id": 1,
        "session_id": 1,
        "gender_preference": 1,
        "video_duration": 1,
        "myJoin.tagger_id": 1
    },
    "query": {
        "video_duration": {
            "$gt": 300
        }
    },
    "sort": [
        {
            "name": "video_duration",
            "direction": -1
        }
    ]
}
4

Welcome to the forum!

How can you tell? Does it return an error? What do the results look like? Which parts work? What happens if you remove the specific fields declaration? What if you remove the query filter?

Your syntax looks good but there’s not enough information to suggest a solution.

5

Found the errors. There were a couple. Posting the query for anyone using Redash with MongoDB and looking to join multiple collections to fetch documents

{
    "collection": "final_report_data",
    
    "aggregate": [
        {
            "$lookup": {
                "from": "hil_tagged_data",
                "localField": "session_id",
                "foreignField": "session_id",
                "as": "tagged_data"
            }
            
        },
        {
            "$unwind": {
                "path": "$tagged_data", 
                "preserveNullAndEmptyArrays": true
                
            }
            
        },
        { 
            "$project": { 
                "session_id": 1,
                "tagging_status": 1,
                "ml_status": 1,
                "tagged_data.tagger_id": 1,
                "tagger_detail.email": 1,
                "tagger_obj_id": { "$toObjectId": "$tagged_data.tagger_id" } 
            } 
            
        },
        {
            "$lookup": {
                "from": "tagger",
                "localField": "tagger_obj_id",
                "foreignField": "_id",
                "as": "tagger_detail"
            }
        },
        {
            "$unwind": {
                "path": "$tagger_detail", 
                "preserveNullAndEmptyArrays": true
            }
        },
        {
            "$limit": 10
            
        }
        
    ],      
    "$sort": { "_id": 1 }
            
    
}
2 Likes
6

I have not been able to query result from multiple collections in mongodb, can anyone help:

indent preformatted text by 4 spaces
{
“collection”: “orders”,
“aggregate”: [
{
“$lookup”: {
“from”: “customers”,
“localField”: “_id”,
“foreignField”: “customerId”,
“as”: “myJoin”
}
},
{
“$unwind”: {
“path”: “$myJoin”,
“preserveNullAndEmptyArrays”: true
}
}
],
“query”: {},
“fields”: {
“_id”: 1,
“$myJoin.name”: 2,
“amount”: 3
}
}