Home > OS >  How to use a value from another key in field path in MongoDB Aggregation?
How to use a value from another key in field path in MongoDB Aggregation?

Time:09-14

Documents

{ color: 'red',
  value: {
    red: {
      level1: {
        level2: 5 
}}}}

{ color: 'blue',
  value: {
    blue: {
      level1: {
        level2: 8
}}}}

How to aggregate the values of value.red.level1.level2 and value.blue.level1.level2?

The keys red and blue come from the key color.

@turivishal requested more info:

I want to use $bucket.

{ '$bucket': {
     groupBy: '$value.*red*.level1.level2',
     boundaries: [1,2,3,4,5,6,7,8,9],
     output: { 
       count: { '$sum': 1 }}}}

The expected result would be

[{ id: 5, count: 1}, { id: 8, count: 1 }]

CodePudding user response:

You can access it by converting it to an array of objects,

  • $objectToArray to convert an object to an array of objects that will convert in k (key) v (value) format
  • $arrayElemAt to get first element from an array, you can use it directly in $bucket's groupBy property
db.collection.aggregate([
  {
    $addFields: {
      value: { $objectToArray: "$value" }
    }
  },
  {
    $bucket: {
      groupBy: { $arrayElemAt: ["$value.v.level1.level2", 0] },
      boundaries: [1, 2, 3, 4, 5, 6, 7, 8, 9],
      output: {
        count: { $sum: 1 }
      }
    }
  }
])

Playground

In the second approach, you can use all operations in the direct $bucket's groupBy property using $let operator,

If you are not using projection stages before $bucket then you can use this approach to avoid the more stages

db.collection.aggregate([
  {
    $bucket: {
      groupBy: {
        $let: {
          vars: { value: { $objectToArray: "$value" } },
          in: { $arrayElemAt: ["$$value.v.level1.level2", 0] }
        }
      },
      boundaries: [1, 2, 3, 4, 5, 6, 7, 8, 9],
      output: {
        count: { $sum: 1 }
      }
    }
  }
])

Playground

CodePudding user response:

I ended up using $addField and $ifNull.

$ifNull takes an array and returns the first value that is not null.

  • Related