Let's say i have following documents:

[
  {
    "key": 1,
    "sub": [
      {
        "id": 4,
        "value": 23
      },
      {
        "id": 1,
        "value": 24
      }
    ]
  },
  {
    "key": 2,
    "sub": [
      {
        "id": 1,
        "value": 92
      },
      {
        "id": 2,
        "value": 93
      }
    ]
  },
  {
    "key": 4,
    "sub": [
      {
        "id": 3,
        "value": 22
      },
      {
        "id": 2,
        "value": 43
      }
    ]
  }
]

I now want to find subdocuments by their id and also see the corresponding parent property key. I have tried following query:

db.collection.aggregate([
  {
    "$match": {
      "sub.id": 1
    }
  },
  {
    "$addFields": {
      "value": {
        "$filter": {
          "input": "$sub",
          "cond": {
            $eq: [
              "$$this.id",
              1
            ]
          }
        }
      }
    }
  },
  {
    "$project": {
      sub: 0
    }
  }
])

This essentially returns the right information:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "key": 1,
    "value": [
      {
        "id": 1,
        "value": 24
      }
    ]
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "key": 2,
    "value": [
      {
        "id": 1,
        "value": 92
      }
    ]
  }
]

But it is not formatted how I need it and also adding more properties from the subdocuments is annoying because of the $addFields. I would rather have it formatted like this:

[
  {
    "id": 1,
    "value": 24,
    "key": 1
  },
  {
    "id": 1,
    "value": 92,
    "key": 2  
  }
]

So I can have just an array of the matching subdocuments with additional parent properties added.

How would I do that?

Mongo Playground

CodePudding user response：

After you have filtered out the unwanted subdocuments, $reduce over the response to build the subdocuments you want to see:

  {"$project": {
      _id: 0,
      "sub": {
        $reduce: {
          input: {
            "$filter": {
              "input": "$sub",
              "cond": {$eq: ["$$this.id", 1]}
            }
          },
          initialValue: [],
          in: {
            $concatArrays: [
              [{
                  key: "$key",
                  id: "$$this.id",
                  value: "$$this.value"
              }],
              "$$value"
            ]
          }
        }
      }
    }
  }

Playground