Writing an aggregation query in MongodB

Question

Here is my sample document structure e.g.

{
    "my_object": {
        "1": {
            "seq": "1",
            "time": "xyz",
        },
        "2": {
            "seq": "2",
            "time": "abc",
            "sub_aray": {
                "0": {
                    "value": 10
                },
                "1": {
                    "value": 10
                },
                "2": {
                    "value": -10
                }
            }
        }
    }
}

So what I want to achieve is, Sum of all the sub_array's value if exists, if sub_array isn't found, default it to 0

{"seq" : "1", "sub_array" : 0},
{"seq" : "2", "sub_array" : 10}

My Mongo version is 3.4.6 and I am using PyMongo as my driver.

I was trying to use map and then just sum the "values" attribute, but the issue is coming when the input to the map's "input" is not present itself. — Aditya
– Aditya, Commented Aug 24, 2020 at 19:27

Gibbs · Accepted Answer · 2020-08-24 19:42:14Z

1

You can do as below

playground

db.collection.aggregate([
  {
    $project: {
      "array": { //To remove dynamic keys - 1,2,etc
        "$objectToArray": "$my_object"
      }
    }
  },
  {//reshaping array
    "$unwind": "$array"
  },
  {
    $project: {//reshaping sub array to access via a static name
      "k": {
        "$objectToArray": "$array.v.sub_aray"
      },
      "seq": "$array.v.seq"
    }
  },
  {
    "$project": {//actual logic, output structure
      "sub_array": {
        $sum: "$k.v.value"
      },
      "_id": 0,
      "seq": 1
    }
  }
])

answered Aug 24, 2020 at 19:42

Gibbs

23.1k14 gold badges84 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aditya Over a year ago

Wow! This is super clean ❤️! Thanks a lot!

Aditya Over a year ago

Gibbs, Would appreciate if you can also share your thoughts on this question of mine stackoverflow.com/q/65857866/6524169 ; Thanks a lot for your help and time!

Valijon · Accepted Answer · 2020-08-24 20:52:04Z

1

The trick part is to use the $objectToArray operator to iterate my_object items.

db.collection.aggregate([
  {
    "$project": {
      "my_object": {
        "$map": {
          input: {
            "$objectToArray": "$my_object"
          },
          as: "obj",
          in: {
            seq: "$$obj.v.seq",
            sub_array: {
              $sum: {
                $map: {
                  input: {
                    "$objectToArray": "$$obj.v.sub_aray"
                  },
                  as: "sub",
                  in: "$$sub.v.value"
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "$unwind": "$my_object"
  },
  {
    "$replaceRoot": {
      "newRoot": "$my_object"
    }
  }
])

MongoPlayground

edited Aug 24, 2020 at 20:52

answered Aug 24, 2020 at 20:45

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

1 Comment

Aditya Over a year ago

Thanks for sharing the map approach as well!❤️

Collectives™ on Stack Overflow

Writing an aggregation query in MongodB

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related