Mongodb $ operator for nested documents [duplicate]

Question

there is a wfs collection as follows:

{
_id:'1',
transitions:
  [
    {
     _id:'11',
     checkLists:
       [
        {
         _id:'111',
         name:'One',
        },
        {
         _id:'112',
         name:'Two',
        }
       ]
     }
  ]
}

I would like to get the sub sub document of _id:'111' I have tried the following code but not working as expected, it returns all of 2nd level nested documents not the proper object

db.wfs.findOne(
    { 'transitions.checkLists._id':  ObjectId('111') },
    { 'transitions.checkLists._id': 1 },
  );

result:

{
transitions: [
  {
    "checkLists": [
      {
        "name": "One",
        "_id": "111"
      },
      {
        "name": "Two",
        "_id": "112"
      }
    ]
  }
]
}

Expected result:

[
  {
    "checkLists": [
      {
        "name": "One",
        "_id": "111"
      }
    ]
  }
]

appreciated any hint or solution

Answer 1

You can use:

db.collection.aggregate([
  {
    $match: {"transitions.checkLists._id": "111"}
  },
  {
    $project: {
      transitions: {
        $reduce: {
          "input": "$transitions",
          initialValue: [],
          in: {$concatArrays: ["$$value", "$$this.checkLists" ]}
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      checkLists: {
        $filter: {
          input: "$transitions",
          as: "item",
          cond: {$eq: ["$$item._id",  "111" ]}
        }
      }
    }
  }
])

As you can see on this playground example.

The $reduce is used to "flatten" your list, and the $filter is used to keep only the part you want.

This is based on this solution by @rickhg12hs to a basically similar, but little more complex, problem. The solution here is just a simple version of it.