MongoDb - Find a specific obj within nested arrays

Question

I hope someone can shed some light on this issue that I have, it's driving me crazy to a point that I have been spending the past three days, learning more and more about mongoDB but still can't figure out this simple query.

What I need to do is to get the object containing the "carId" = "3C".

In other words the object that I want the query to return is:

            {
                "carId" : "3C", 
                "_id" : ObjectId("51273329b64f07a40ef1c15e")
            }

Here is the dataset (cars):

   { 
        "_id" : ObjectId("56223329b64f07a40ef1c15c"), 
        "username" : "john", 
        "email" : "john@john.com", 
        "accounts" : [
            {
                "_id" : ObjectId("56322329b61f07a40ef1c15d"), 
                "cars" : [
                    {
                        "carId" : "6A", 
                        "_id" : ObjectId("56323329b64f07a40ef1c15e")
                    },
                    {
                        "carId" : "6B", 
                        "_id" : ObjectId("56323329b64f07a40ef1c15e")
                    }
                ]
            }
        ]
    },
    { 
        "_id" : ObjectId("56223125b64f07a40ef1c15c"), 
        "username" : "paul", 
        "email" : "paul@paul.com", 
        "accounts" : [
            {
                "_id" : ObjectId("5154729b61f07a40ef1c15d"), 
                "cars" : [
                    {
                        "carId" : "5B", 
                        "_id" : ObjectId("56323329854f07a40ef1c15e")
                    }
                ]
            },
            {
                "_id" : ObjectId("56322117b61f07a40ef1c15d"), 
                "cars" : [
                    {
                        "carId" : "6G", 
                        "_id" : ObjectId("51212929b64f07a40ef1c15e")
                    },
                    {
                        "carId" : "3C", 
                        "_id" : ObjectId("51273329b64f07a40ef1c15e")
                    },
                    {
                        "carId" : "4N", 
                        "_id" : ObjectId("51241279b64f07a40ef1c15e")
                    }
                ]
            }
        ]
    }

Please note that I have two nested arrays, and apparently MongoDb lacks when it comes to dealing with Projections with deep arrays. The $ operator can only be used once in a projection; leaving with no clues as how to to achieve this simple task.

So again I want to find --only-- the document that has "carId" : "3C" and only return the immediate obj containing the "carId" : "3C". but not the parent objects.

Any help would be so much appreciated. Possibly using either direct MongoDb or Mongoose. Mongoose would be preferred.

As for reference, I have already covered these other related issues wasn't able to figure it out.

Updating a deep record in MongoDb

How to Update Multiple Array Elements in mongodb

Hope in the future, this question and your solutions will help others.

Answer 1

Amir,

You must use the Aggregation Framework. You can build a pipeline that processes a stream documents through several building blocks: filtering, projecting,grouping,sorting,etc.

When dealing with nested arrays you will have to use the $unwind command. You can get what you want by doing the following.

db.cars.aggregate(
    //De-normalized the nested array of accounts
    {"$unwind": "$accounts"},
    //De-normalized the nested array of cars
    {"$unwind": "$accounts.cars"},
    //match carId to 3C
    {"$match": {"accounts.cars.carId" : "3C"}},
    //Project the accoutns.cars object only
    {"$project" : {"accounts.cars" : 1}},
    //Group and return only the car object
    {"$group":{"_id":"$accounts.cars"}}
).pretty();

You can use the aggregation framework for "array filtering" by using $unwind .

You can delete each line from the bottom of each command in the aggregation pipeline in the above code to observe the pipelines behavior.

Answer 2

Here's an example without the aggregation framework. I don't think there's a way purely from querying that you'll be able to get just the individual nested object you're looking for so you have to do a little post processing work. something like Mongoose may provide a way to do this but I'm not really up on what the Mongoose API's look like currently.

var doc = db.cars.findOne({"accounts.cars" : {$elemMatch: {"carId" : "3C"}}}, {"accounts.cars.$": 1, _id: 0})
var car = doc.accounts[0].cars[0]