Mongodb use find (not aggregation) to get only one nested sub-document

user_10669723 0
Viewed 82

In my collection I have two nested sub-documents, respectively called "levels" and "modules":

[
  {
    "_id": 1,
    "course": "course1",
    "levels": [
      {
        "level_id": "C1L1",
        "name": "course 1 - level1",
        "modules": [
          {
            "module_id": "C1L1M1",
            "name": "level 1module1"
          },
          {
            "module_id": "C1L1M2",
            "name": "level 1 module2"
          },
          
        ]
      },
      {
        "level_id": "C1L2",
        "name": "course 1 - level2",
        "modules": [
          {
            "module_id": "C1L2M1",
            "name": "level 2 module1"
          },
          {
            "module_id": "C1L1M2",
            "name": "level 2 module2"
          },
          
        ]
      },
      
    ]
  },
  {
    "_id": 2,
    "course": "course2",
    "levels": [
      {
        "level_id": "C2L1",
        "name": "course 2 - level1",
        "modules": [
          {
            "module_id": "C2L1M1",
            "name": "level1 module1"
          },
          {
            "module_id": "C2L1M2",
            "name": "level1 module2"
          },
          
        ]
      },
      {
        "level_id": "C2L2",
        "name": "course 2 - level2",
        "modules": [
          {
            "module_id": "C2L2M1",
            "name": "level 2 module1"
          },
          {
            "module_id": "C2L1M2",
            "name": "level 2 module2"
          },
          
        ]
      },
      
    ]
  },
]

I need to get only one sub-document that matches criteria, for example {"module_id": "C1L2M1"} . I already know exactly how to achieve this using the aggregation framework, but I want to know if there is any solution avoiding aggregation.

I can retrieve only one nested sub-document using $elemMatch inside the projection, but I can't figure out how to filter the second nested sub-document (modules, in my example).

My desired result is query module "C1L2M1" and get only this:

{
  "module_id": "C1L2M1",
  "name": "level 2 module1"
}

Here an example playground: https://mongoplayground.net/p/uwBOh1MkQm-

mongodb-query mongodb
2 Answers

here's an unreadable mess that get's the result you want using find command:

db.collection.find(
{
    "levels.modules.module_id": "C1L2M1"
},
{
    _id: 0,
    module: {
        $first: {
            $filter: {
                input: {
                    $map: {
                        input: "$levels",
                        as: "l",
                        in: {
                            $first: {
                                $filter: {
                                    input: "$$l.modules",
                                    as: "m",
                                    cond: { $eq: ["$$m.module_id", "C1L2M1"] }
                                }
                            }
                        }
                    }
                },
                as: "x",
                cond: { $ne: ["$$x", null] }
            }
        }
    }
})

it outputs the following:

{
    "module" : {
        "module_id" : "C1L2M1",
        "name" : "level 2 module1"
    }
}

https://mongoplayground.net/p/KZHwvKdgY9f

user_43684850

I want to know if there is any solution avoiding aggregation.

There is no other option except aggregation, To achieve your expected result you must have to use Aggregation Array Expression Operators.

If you really want to use find methods then you can use Aggregation Expression starting from MongoDB 4.4,

Starting in MongoDB 4.4, as part of making find() and findAndModify() projection consistent with aggregation's $project stage,
The find() and findAndModify() projection can accept aggregation expressions and syntax.
MongoDB enforces additional restrictions with regards to projections. See Projection Restrictions for details.

do u know if using aggregation operator inside find projection is faster or slower (or equal :) ) in terms of performance?

As per find-projection-documentation there is no any specification about performance, but i think both are equal.

If you really want to check the performance of both methods then you can check by explain() executionStats mode.

user_89871280
Related