MongoDB - simple sub query example

mongodb, nested, pymongo, subquery

Solution

You can do this within a single query using the aggregation framework. In particular you'd need to run an aggregation pipeline that uses the `$lookup` operator to do a left join from the `parameters` collection to the `groups` collection.

Consider running the following pipeline:

db.parameters.aggregate([
    { "$unwind": "$groups" },
    {
        "$lookup": {
            "from": "groups",
            "localField": "groups",
            "foreignField": "_id",
            "as": "grp"
        }
    },
    { "$unwind": "$grp" }
])

Sample Output

/* 1 */
{
    "_id" : ObjectId("56cac0cd0b5a1ffab1bd6c12"),
    "name" : "Speed",
    "groups" : "123",
    "grp" : {
        "_id" : "123",
        "name" : "Group01"
    }
}

/* 2 */
{
    "_id" : ObjectId("56cac0cd0b5a1ffab1bd6c12"),
    "name" : "Speed",
    "groups" : "234",
    "grp" : {
        "_id" : "234",
        "name" : "Group02"
    }
}

If your MongoDB server version does not support the `$lookup` pipeline operator, then you'd need execute two queries as follows:

# get the group ids
ids = db.parameters.find_one({ "_id": ObjectId("56cac0cd0b5a1ffab1bd6c12") })["groups"]

# query the groups collection with the ids from previous query
db.groups.find({ "_id": { "$in": ids } })

EDIT: matched the field name in the aggregation query to the field name in example dataset (within the question)

Problem

Given the data: ``` > db.parameters.find({}) { "_id" : ObjectId("56cac0cd0b5a1ffab1bd6c12"), "name" : "Speed", "groups" : [ " 123", "234" ] } > db.groups.find({}) { "_id" : "123", "name" : "Group01" } { "_id" : "234", "name" : "Group02" } { "_id" : "567", "name" : "Group03" } ``` I would like to supply a parameter _id an make a query return all groups that are within the groups array of the given document in parameters table. The straightforward solution seems to make several DB calls in PyMongo: - Get parameter from parameters table based on the supplied _id - For each element of groups array select a document from groups collection But this will have so much unnecessary overhead. I feel there must be a better, faster way to do this within MongoDB (without running custom JS in the DB). Or should I re-structure my data by normalising it a little bit (like a table of relationships), neglecting the document-based approach? Again, please help me find a solution that would work from PyMongo DB interface

Original source