Please, help , I have following document type:

{
_id: 1,
"_a": [
  {
    "_aId": {
      "CC": "CA"
    },
    "_p": [
      {
        "_pId": {
          "CC": "CA"
        },
        "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 for language:"CA" & format:"A4" for documents with "_a._p._pid.CC":"CA"

So the expected output need to look as follow:

  {sId:1}
  {sId:4}
  {sId:10}

I have an index on "_a._p._pId.CC" and I have tried with 2x $unwind , but collection is pretty big and it take some time to unwind , any suggestion highly is welcome?

Playground

mongod 4.0.23

CodePudding user response：

Edit: rmoved $first to match mongodb version 3.6:

One option is:

1. $reduce to res the objects under _a._p.s
2. Use $objectToArry, since there are different key names.
3. $reduce again to get a flat array of ts.
4. $filter the array to keep only wanted items.
5. Format the response.

db.collection.aggregate([
  {$project: {
      _id: 0,
      res: {$reduce: {
          input: "$_a",
          initialValue: [],
          in: {$concatArrays: [
              "$$value",
              {$reduce: {
                  input: "$$this._p",
                  initialValue: [],
                  in: {$concatArrays: ["$$value", ["$$this.s"]]}
                }
              }
            ]
          }
        }
      }
    }
  },
  {$project: {res: {$map: {input: "$res", in: {$objectToArray: "$$this"}}}}},
  {$project: {
      res: {
        $reduce: {
          input: "$res",
          initialValue: [],
          in: {$concatArrays: [
              "$$value",
              {$reduce: {
                  input: "$$this",
                  initialValue: [],
                  in: {$concatArrays: ["$$value", "$$this.v.t"]}}
              }
            ]
          }
        }
      }
    }
  },
  {$project: {
      res: {$filter: {
          input: "$res",
          cond: {
            $and: [{$eq: ["$$this.language", "CA"]}, {$eq: ["$$this.format", "A4"]}]
          }
        }
      }
    }
  },
  {$unwind: "$res"},
  {$project: {sId: "$res.sId"}}
])

See how it works on the playground example