How to query multiple collection in mongodb?

Question

I have a user collection which has a _id field.
A user can have a subscription for a api key, hence userID is stored in subscription collection
Another collection for Api is there which has subscriptionID

subscription collection:

{
    "_id":ObjectId("60924ec559bedd45dd74bd61"),
    "userID":ObjectId("6090f048536d345a024b8b60")
}

{
    "_id":ObjectId("609141bd392194fde3ba7cf6"),
    "userID":ObjectId("6090f048536d345a024b8b60")
}
{
    "_id":ObjectId("609141bd392194fde3ba7c54"),
    "userID":ObjectId("6090f048536d345a024b8bc2")
}

Api collection:

{
   "_id":ObjectId("60924e7426ed2445aab2fc7f"),
   "subscriptionID":ObjectId("609143fb036c8a007a5e6a61")
}
{
   "_id":ObjectId("60924ec559bedd45dd74bd61"),
   "subscriptionID":ObjectId("609141bd392194fde3ba7cf6")
}
{
   "_id":ObjectId("60924ec559bedd45dd74bd68"),
   "subscriptionID":ObjectId("609141bd392194fde3ba7c54")
}

Now I want to get all the api keys of a user using userID

means I will go through the Api collection find subscriptionID => and then go to the subscription collection and => match the userID with the provided userID

Note- subscriptionID in api collection is unique(a subscription has only one api key) userID is not unique in subscription collection.

input: userID - 6090f048536d345a024b8b60
output:  first two api documents

This looks like you migrated from RDBMS (e.g. Oracle) to MonogDB and converted tables into collections one-by-one. Often this is a poor design, usually the number of collection in MongoDB is much lower than the number of tables in your RDBMS. — Wernfried Domscheit
– Wernfried Domscheit, Commented May 6, 2021 at 6:21
No its not migrated, I started it in mongodb only, I have now embedded the userID in api collection — Vinita
– Vinita, Commented May 6, 2021 at 6:32
Can you suggest me a better way to implement this, since my project is in its initial phase only and I can still change the way my schema and collections are designed — Vinita
– Vinita, Commented May 6, 2021 at 6:34

hhharsha36 · Accepted Answer · 2021-05-06 04:49:47Z

The way you have implemented your MongoDB schema is a very bad practice. Try to embed the keys wherever you want to reference other collections to be direct.

The below query will work but will consume a lot of time if the number of records to look up for is very high.

db.getCollection('user').aggregate([
{
    "$match": {
        "_id" : {
            "$in": [
            ObjectId("6090f048536d345a024b8bc2"), 
            ObjectId("6090f048536d345a024b8b60")]
        }
    }
},
{
    "$lookup": {
        "from": "subscription",
        "let": {"u_id": "$_id"},
        "pipeline": [
            {
                "$match": {
                    "$expr": {
                        "$eq": ["$userID", "$$u_id"],
                    }
                }
            },
            {
                "$project": {
                    "_id": 1
                }
            },
        ],
        "as": "subscriptionData"
    }
},
{
    "$unwind": {
        "path": "$subscriptionData", 
        "preserveNullAndEmptyArrays": false,
    }
},
{
    "$lookup": {
        "from": "api",
        "let": {"sub_id": "$subscriptionData._id"},
        "pipeline": [
            {
                "$match": {
                    "$expr": {
                        "$eq": ["$subscriptionID", "$$sub_id"],
                    }
                }
            },
            {
                "$project": {
                    "_id": 1
                }
            },
        ],
        "as": "apiData"
    }
},
{
    "$unwind": {
        "path": "$apiData", 
        "preserveNullAndEmptyArrays": false,
    }
},
{
    "$group": {
        "_id": "$_id",
        "subscriptionIds": {"$addToSet": "$subscriptionData._id"},
        "apiIds": {"$addToSet": "$apiData._id"},
    },
},
])

This query returns the below output for the input sample provided.

[
{
    "_id" : ObjectId("6090f048536d345a024b8b60"),
    "subscriptionIds" : [ 
        ObjectId("609141bd392194fde3ba7cf6")
    ],
    "apiIds" : [ 
        ObjectId("60924ec559bedd45dd74bd61")
    ]
},
{
    "_id" : ObjectId("6090f048536d345a024b8bc2"),
    "subscriptionIds" : [ 
        ObjectId("609141bd392194fde3ba7c54")
    ],
    "apiIds" : [ 
        ObjectId("60924ec559bedd45dd74bd68")
    ]
}
]

I was not expecting this big query, I would embed userID in api collection, it seems better way as you suggested. Thanks

Collectives™ on Stack Overflow

How to query multiple collection in mongodb?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related