CODEWITHSUNDEEP
mongodbaggregation-framework
Faysal Ahmed
Faysal Ahmed

Reputation: 1542

dynamically access variable keys in mongodb aggregation framework

In my aggregation pipeline one of the stage($group) is similar to this

$group: {
    _id: '$_id',
    dist: { $first: '$dist' },
    sub_job_matrix: {
      $sum: {
        $let: {
          vars: {
            matrix: {
              a: 1,
              b: 2,
              c: 3
            }
          },
          in: {
            $multiply: [
              '$$matrix.?', // *** the key will be dependent on the value of `$experience.category`
              { $divide: ['$experience.duration', 12] }]
          }
        }
      }
    },
    count: { $sum: 1 }
  }

i'm not sure how do i solve this problem. or can problem be solved in an alternative approach?

Upvotes: 0

Views: 1013

Answers (1)

Alex Blex
Alex Blex

Reputation: 37048

Not very elegant, yet you can do it with $switch

{
    $sum: {
        $multiply: [
            {
                $switch:
                    {
                        branches: [
                            {
                                case: {$eq: ["$experience.category", "a"]},
                                then: 1
                            },
                            {
                                case: {$eq: ["$experience.category", "b"]},
                                then: 2
                            },
                            {
                                case: {$eq: ["$experience.category", "c"]},
                                then: 3
                            },
                        ],
                        default: "Never happens"
                    }
            },
            { $divide: ['$experience.duration', 12] }
        ]
    }
}

Update

The branches array can be built clientside. A javascript example to convert 2d matrix:

const matrix =  {
    sales: {bartender:2, cleaning: 4, construction: 3},
    bartender: {sales:5, cleaning: 4, construction: 3}
}

let branches = [];

for (const [key1, obj] of Object.entries(matrix)) {
    for (const [key2, value] of Object.entries(obj)) {
        branches.push({
            "case": { "$and": [ 
                {"$eq": ["$some_field", key1]}, 
                {"$eq": ["$another_field", key2]}
            ]},
            then: value
        })
    }
}

so the $multiply expression in the pipeline would be

    $multiply: [
        { $switch: {
            branches: branches
            default: "Never happens"
        } }, 
        { $divide: ['$experience.duration', 12] }
    ]

Upvotes: 1

Related Questions