fetch elements from 3x nested array avoiding $unwind

Question

I have the following document type:

{
  "_id": 1,
  "_a": [
    {
      "_aId": {
        "CC": "CA"
      },
      "_p": [
        {
          "_pId": {
            "CC": "CA",
            "SN": 1
          },
          "s": {
            "c": {
              "t": [
                {
                  "sId": 1,
                  "language": "CA",
                  "format": "A4"
                },
                {
                  "sId": 2,
                  "language": "JP",
                  "format": "A4"
                }
              ]
            },
            "a": {
              "t": [
                {
                  "sId": 4,
                  "language": "CA",
                  "format": "A4"
                },
                {
                  "sId": 5,
                  "language": "EN",
                  "format": "A3"
                }
              ]
            },
            "d": {
              "t": [
                {
                  "sId": 10,
                  "language": "CA",
                  "format": "A4"
                }
              ]
            }
          }
        }
      ]
    }
  ]
}

And I need faster solution to extract all subdocuments _aId, sId & _pid for language:"CA" & format:"A4" for documents with "_a._p._pid.CC":"CA"

The expected output is as follow:

{_aId:{"CC": "CA"},_pid:{CC:"CA",SN:1},sId:1}
{_aId:{"CC": "CA"},_pid:{CC:"CA",SN:1},sId:4}
{_aId:{"CC": "CA"},_pid:{CC:"CA",SN:1},sId:10}

Very similar to what @Turvishal and @Nimrod helped to solve here But not very sure how to add also the _aId in the final output...

@Turvishal/@Nimrod solution for the previous part was excellent in the playground here , helped to avoid the heavy $unwind that I have used before , but now need to include the _aId from the root array which seems abit complex without $unwind , please, help?

( mongodb sharded cluster 4.2 )

Answer 1

Based on previous solutions, we just need to add it to the second step using $mergeObjects:

db.collection.aggregate([
  {$match: {"_a._p._pId.CC": "CA"}},
  {$project: {
      _id: 0,
      _aId: "$_a._aId",
      _a: {
        $reduce: {
          input: "$_a",
          initialValue: [],
          in: {$concatArrays: [
              "$$value",
              {$map: {
                  input: {
                    $filter: {
                      input: "$$this._p",
                      cond: {$eq: ["$$this._pId.CC", "CA"]}}
                  },
                  as: "i",
                  in: {$mergeObjects: ["$$i", {_aId: "$$this._aId"}]}
                }
              }
            ]
          }
        }
      }
    }
  },
  {$project: {
      _a: {$reduce: {
          input: "$_a",
          initialValue: [],
          in: {$concatArrays: [
              "$$value",
              {$map: {
                  input: {
                    $reduce: {
                      input: {$objectToArray: "$$this.s"},
                      initialValue: [],
                      in: {$concatArrays: [
                          "$$value",
                          {$filter: {
                              input: "$$this.v.t",
                              cond: {$and: [
                                  {$eq: ["$$this.format", "A4"]},
                                  {$eq: ["$$this.language", "CA"]}
                                ]
                              }
                            }
                          }
                        ]
                      }
                    }
                  },
                  as: "p",
                  in: {_aId: "$$this._aId", _pId: "$$this._pId", sId: "$$p.sId"}
                }
              }
            ]
          }
        }
      }
    }
  },
  {$unwind: "$_a"},
  {$replaceRoot: {newRoot: "$_a"}}
])

See how it works on the playground example

The last unwind is only needed if you want to break the document into several documents as in your format. If you want to keep them all in one result document, you can ignore the last two phases.