MongoDB Aggregation project field to be conditional value

user_5449823 0
Viewed 639

I try to project my latest aggregation artifact, into a new one, with an additional field, whose value should be conditional.

My latest artifact of the aggregation pipeline is:

[
    server: {
      _id: 6108d87b9255993b26796ca9,
      version: '2.0.366',
      name: 'aaaaaa',
      variables: [
          {
              key: 'aKey',
              value: 'aVal',
          },
          {
              key: 'bKey',
              value: 'bVal',
          },
          {
              key: 'cKey',
              value: 'cVal',
          }
      ],
      __v: 0
    }
]

So basically I want to project the additional artifact with an additional field whose name is bucketName.

I want to check in the variables array, whether there is an item, whose key is pre-known of value aKey. If so, the result of the projected bucketName would be the value field: aVal. If could not find, value would be NOT_FOUND.

So the result of the sample above would be:

[
    server: {
      _id: 6108d87b9255993b26796ca9,
      version: '2.0.366',
      name: 'aaaaaa',
      variables: [
          {
              key: 'aKey',
              value: 'aVal',
          },
          {
              key: 'bKey',
              value: 'bVal',
          },
          {
              key: 'cKey',
              value: 'cVal',
          }
      ],
      bucketName: 'aVal',
      __v: 0
    }
]

My try was:

{
    $project: {
        bucketName: {
            $cond: {
                if: {
                    $eq : ['$server.variables.key', 'aKey'],
                },
                then: '$server.variables.value',
                else: 'NOT_FOUND',
            },
        },
    },
},

But that just didn't do the query correctly.

aggregation-framework mongodb
3 Answers

You should use $in operator because this variables key is array:

Then, use also $filter within the then to extract that value.

db.collection.aggregate([
  {
    "$set": {
      "bucketName": {
        "$cond": {
          "if": {
            "$in": [
              "aKey",
              "$server.variables.key"
            ]
          },
          "then": {
            "$filter": {
              "input": "$server.variables",
              "as": "variable",
              "cond": {
                "$eq": [
                  "$$variable.key",
                  "aKey"
                ]
              }
            },
            
          },
          "else": "NOT_FOUND"
        }
      }
    },
    
  },
  {
    "$unwind": "$bucketName",
    
  },
  {
    "$project": {
      "bucketName": "$bucketName.value",
      "server": 1
    }
  }
])
user_91052070
  • you can check $in condition because server.variables.key will return an array of string
  • $indexOfArray to get index of matching key element from array server.variables.key
  • $arrayElemAt to select value from server.variables.value by about returned index
db.collection.aggregate([
  {
    $project: {
      bucketName: {
        $cond: {
          if: { $in: ["aKey", "$server.variables.key"] },
          then: {
            $arrayElemAt: [
              "$server.variables.value",
              { $indexOfArray: ["$server.variables.key", "aKey"] }
            ]
          },
          else: "NOT_FOUND"
        }
      }
    }
  }
])

Playground

user_89871280

Your $server.variables.key is an array, is that why $eq fails, you are comparing an string with an array. You need to use $in. Check this example.

But also there is a problem, as $server.variables.key is an array with all keys values, the bucketName variable will be an array too.

You have to use the string aKey into then stage like this

db.collection.aggregate({
  "$set": {
    "bucketName": {
      "$cond": {
        "if": {
          "$in": [
            "aKey",
            "$server.variables.key"
          ]
        },
        "then": "aKey",
        "else": "NOT_FOUND"
      }
    }
  }
})
user_134642790
Related