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 sId & _pid for language:"CA" & format:"A4" for documents with "_a._p._pid.CC":"CA"

So the expected output need to look as follow:

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

Playground

Thanks to @Nimrod I have the initial solution here, but not sure how to include the _pid as well so it does not reduce performance ...

(I am using MongoDB 4.0)

CodePudding user response：

I am not sure about performance, but you can try an option,

• $match your condition,
• $project,
  • $reduce to iterate loop of _a
    • $filter to iterate loop of _p and filter it by "_pId.CC": "CA"
    • $concatArrays to concat above filtered result with previous filtered result
• $project, the process is hard to describe here but it just filters your expected fields in the result
• $unwind to deconstruct the _a

db.collection.aggregate([
  { $match: { "_a._p._pId.CC": "CA" } },
  {
    $project: {
      _id: 0,
      _a: {
        $reduce: {
          input: "$_a",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              {
                $filter: {
                  input: "$$this._p",
                  cond: { $eq: ["$$this._pId.CC", "CA"] }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $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: {
                    _pId: "$$this._pId",
                    sId: "$$p.sId"
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  { $unwind: "$_a" }
])

Playground