Count values in array of objects

Working with Data
1

Hello

Is there a more efficient way to count specific values from an array of object rather than sending the entire array to the client and “unwind” it there?

Test data:

{
	"name": "Test",
	"votes": [
		{
			"user": "joe",
			"action": "up"
		},
		{
			"user": "john",
			"action": "up"
		},
		{
			"user": "emma",
			"action": "down"
		}		
	]
}

I’d like to get something like:

{
	"up": 2,
	"down": 1
}

so i can calculate a rating. BTW, I’m using GO. I think I could translate the script from shell and most other languages.

Thanks

2

Stages of a pipeline, including $unwind are done on the server. The array is not sent to the client, just the final result of the pipeline. This means if $unwind is the last stage of the pipeline, then yes the array is sent over. If you have a $group, like to count specific values, the client only receives the counts.

3

hello

You can use $reduce ,and keep the array as it is

And make the counting ,without the need to $unwind on server or do something like “unwind” on the driver,on driver only the reduce results could be sended.

1 Like
4

could somebody please give an example for that? I’m all new to Mongo…

5

Hello

Reduce read the array and sums
Filter read the array 2 times,one sum the up the other sums the downs
Unwind the votes,replace each document with {up 1} or {down 1} depend on what it voted.
Group and sum.

I think the easier is the filter,just reads the array 2 times(reduce reads it 1 time)
I think it does what you want.

1)Run Reduce

db.collection.aggregate([
  {
    "$project": {
      "_id": 0,
      "upDown": {
        "$reduce": {
          "input": "$votes",
          "initialValue": [
            0,
            0
          ],
          "in": {
            "$let": {
              "vars": {
                "votes": "$$value",
                "vote": "$$this"
              },
              "in": {
                "$cond": [
                  {
                    "$eq": [
                      "$$vote.action",
                      "up"
                    ]
                  },
                  [
                    {
                      "$add": [
                        {
                          "$arrayElemAt": [
                            "$$votes",
                            0
                          ]
                        },
                        1
                      ]
                    },
                    {
                      "$arrayElemAt": [
                        "$$votes",
                        1
                      ]
                    }
                  ],
                  [
                    {
                      "$arrayElemAt": [
                        "$$votes",
                        0
                      ]
                    },
                    {
                      "$add": [
                        {
                          "$arrayElemAt": [
                            "$$votes",
                            1
                          ]
                        },
                        1
                      ]
                    }
                  ]
                ]
              }
            }
          }
        }
      }
    }
  },
  {
    "$project": {
      "up": {
        "$arrayElemAt": [
          "$upDown",
          0
        ]
      },
      "down": {
        "$arrayElemAt": [
          "$upDown",
          1
        ]
      }
    }
  }
])

2)Run Filter

db.collection.aggregate([
  {
    "$project": {
      "_id": 0,
      "up": {
        "$size": {
          "$filter": {
            "input": "$votes",
            "as": "vote",
            "cond": {
              "$eq": [
                "$$vote.action",
                "up"
              ]
            }
          }
        }
      },
      "down": {
        "$size": {
          "$filter": {
            "input": "$votes",
            "as": "vote",
            "cond": {
              "$eq": [
                "$$vote.action",
                "down"
              ]
            }
          }
        }
      }
    }
  }
])

3)Run unwind and group

db.collection.aggregate([
  {
    "$unwind": {
      "path": "$votes"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$cond": [
          {
            "$eq": [
              "$votes.action",
              "up"
            ]
          },
          {
            "up": 1
          },
          {
            "down": 1
          }
        ]
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "up": {
        "$sum": "$up"
      },
      "down": {
        "$sum": "$down"
      }
    }
  },
  {
    "$project": {
      "_id": 0
    }
  }
])
2 Likes
6

wow that’s way more complex than I expected. Going to be tricky to “translate” this for the Golang driver. Thanks :slight_smile:

7

Something like:

db.collection.aggregate([  
    { "$unwind": "$votes" },
    { "$group": {
      "_id": "$name",
      "ups": {$sum: {$cond: {"if": {"$eq": ["$votes.action","up"]},then: 1,else: 0}}},
      "not_ups": {$sum: {$cond: {"if": {"$ne": ["$votes.action","up"]},then: 1,else: 0}}},
      "score": {$sum: {$cond: {"if": {"$eq": ["$votes.action","up"]},then: 1,else: -1}}}}
    }])

should get you there…
You might want to account for “dirty” data where an action is neither up nor down in some way too?

Check out Mongo playground

1 Like
8

I have a simpler answer for you:

db.test01.aggregate([
… { $unwind: “$votes” },
… { $sortByCount: “$votes.action” }
… ])
{ “_id” : “up”, “count” : 2 }
{ “_id” : “down”, “count” : 1 }

All the best,

– Rodrigo

3 Likes
9

Does it allocate vote per name field?

10

It sorts by and counts across all documents in the collection.

Collection:

db.test01.find()
{ “_id” : ObjectId(“5fd906d828eb4534ccbceb9a”), “name” : “Test”, “votes” : [ { “user” : “joe”, “action” : “up” }, { “user” : “john”, “action” : “up” }, { “user” : “jane”, “action” : “down” } ] }
{ “_id” : ObjectId(“5fd90b3728eb4534ccbceb9b”), “name” : “Test1”, “votes” : [ { “user” : “joe”, “action” : “up” }, { “user” : “john”, “action” : “up” }, { “user” : “jane”, “action” : “down” } ] }

Aggregation result:

db.test01.aggregate([ { $unwind: “$votes” }, { $sortByCount: “$votes.action” } ])
{ “_id” : “up”, “count” : 4 }
{ “_id” : “down”, “count” : 2 }

Just add a $match to get per name:

db.test01.aggregate([ { $match: { name: “Test” } }, { $unwind: “$votes” }, { $sortByCount: “$votes.action” } ])
{ “_id” : “up”, “count” : 2 }
{ “_id” : “down”, “count” : 1 }

All the best,

– Rodrigo

3 Likes
11

One of the thing that I do is to never have my aggregation pipeline in the native language I am using. I always keep them as a json document that I parsed at run time. This way I can use the same source pipeline code whatever I playing with the shell or coding in a specific language. My pipelines become like a dynamic library.

12

Nice! I missed the $match the first time around.

13

Do you think this logic can be integrated with a FindOne Projection? As you can see in my sample data, the “votes” array is part (nested) of another “parent” object (I’ve included only a “name” field here) - my goal is to read that parent document - probably as a projection - and aggregate those votes…

14

Hey,

I don’t believe you can use FindOne. But here is a slightly modified version that allows you to grab all information you want from the parent document and also count the votes.

> db.test01.aggregate([
... { $match: { name: "Test" } },
... { $facet: {
...     "parent_fields": [
...         { $project: { name: 1 } }
...     ],
...     "counted_votes": [
...         { $unwind: "$votes" },
...         { $sortByCount: "$votes.action" }
...     ]
...   }
... }
... ])
{ "parent_fields" : [ { "_id" : ObjectId("5fd906d828eb4534ccbceb9a"), "name" : "Test" } ], "counted_votes" : [ { "_id" : "up", "count" : 2 }, { "_id" : "down", "count" : 1 } ] }
>

I hope it helps.

All the best,

– Rodrigo

2 Likes