I have this document:

[
  {
    "name": "Report1",
    "specifications": [
      {
        "parameters": [
          {
            "name": "feature",
            "value": [
              "13"
            ]
          },
          {
            "name": "security",
            "value": [
              "XXXX-695"
            ]
          },
          {
            "name": "imageURL",
            "value": [
              "football.jpg"
            ],
            
          }
        ]
      }
    ]
  },
  {
    "name": "Report2",
    "specifications": [
      {
        "parameters": [
          {
            "name": "feature",
            "value": [
              "67"
            ]
          },
          {
            "name": "imageURL",
            "value": [
              "basketball.jpg"
            ],
            
          },
          {
            "name": "security",
            "value": [
              "XXXX-123"
            ]
          }
        ]
      }
    ]
  }
]

I want to obtain specifications[0].parameters.value[0] where parameters.name = "imageUrl". Like that:

[
  {
    "imageparam": "football.jpg",
    "name": "Report1"
  },
  {
    "imageparam": "basketball.jpg",
    "name": "Report2"
  }
]

I use MongoDB 3.6.3 with MongoDB Compass. I want to use aggregation (to add a pipeline in MongoDb Compass) so I could write finally this aggregation but it has 5 $project stage. Is there any more efficient or better solution:

db.collection.aggregate([{$project: {
  _id: 0,
  name: 1,
  specifications: {$arrayElemAt: ["$specifications", 0]}
}}, {$project: {
  name: 1,
  imageparam: {
    $filter: {
      input: '$specifications.parameters',
      as: 'param',
      cond: {
        $eq: [
          '$$param.name',
          'imageURL'
        ]
      }
    }
  }
}}, {$project: {
  name: 1,
    imageparam: {$arrayElemAt: ["$imageparam",0]}
}}, {$project: {
  name: 1,
    imageparam: "$imageparam.value"
}}, {$project: {
  name: 1,
  imageparam: {$arrayElemAt: ["$imageparam",0]}
}}])

This is playground.

CodePudding user response：

You can reduce it to 2 stages, might be there will be other options as well,

• pass directly $arrayElemAt of specifications.parameters to $filter input and find the matching value for imageURL
• use can use $addFields or $set stage to get first element from return result

db.collection.aggregate([
  {
    $project: {
      _id: 0,
      name: 1,
      imageparam: {
        $arrayElemAt: [
          {
            $filter: {
              input: { $arrayElemAt: ["$specifications.parameters", 0] },
              cond: { $eq: ["$$this.name", "imageURL"] }
            }
          },
          0
        ]
      }
    }
  },
  {
    $addFields: {
      imageparam: { $arrayElemAt: ["$imageparam.value", 0] }
    }
  }
])

Playground

The second option you can do it in single stage using $let to bind the variables for use in the specified expression,

db.collection.aggregate([
  {
    $project: {
      _id: 0,
      name: 1,
      imageparam: {
        $let: {
          vars: {
            param: {
              $arrayElemAt: [
                {
                  $filter: {
                    input: { $arrayElemAt: ["$specifications.parameters", 0] },
                    cond: { $eq: ["$$this.name", "imageURL"] }
                  }
                },
                0
              ]
            }
          },
          in: { $arrayElemAt: ["$$param.value", 0] }
        }
      }
    }
  }
])

Playground