CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
greg
greg

Reputation: 1416

Group by several fields and custom sums with two conditions

I want to group rows with two conditions. The first one to get total (now it works), the second to get unread messages. I cannot imagine how to do it. Inserts are:

db.messages.insert({'source_user': 'test1', 'destination_user': 'test2', 'is_read': true})
db.messages.insert({'source_user': 'test1', 'destination_user': 'test2', 'is_read': false})
db.messages.insert({'source_user': 'test1', 'destination_user': 'test3', 'is_read': true})

my code:

db.messages.aggregate([
    {'$match': {'source_user': user}},
    {'$group': {
        '_id': {
            'source_user': '$source_user',
            'destination_user': '$destination_user',
            'is_read': '$is_read'
        },
        'total': {'$sum': 1}}
    },
    {'$project': {
        'source_user': '$_id.source_user',
        'destination_user': '$_id.destination_user',
        #'unread': {'$sum': {'$_id.is_read': False}},
        'total': '$total',
        '_id': 0
    }}
    ])

as a result I want to get:

[{
    'source_user': 'test1',
    'destination_user': 'test2',
    'unread': 1,
    'total': 2
  }, {
    'source_user': 'test1',
    'destination_user': 'test3',
    'unread': 0,
    'total': 1
 }
]

Should I add a new group or I can use $is_read flag in the same group?

Thank you!

Upvotes: 1

Views: 29

Answers (1)

mickl
mickl

Reputation: 49945

You can count unread messages the same way you do it for total but you need to apply $cond to add 0 only for those that are read and 1 for other ones:

db.messages.aggregate([
    {'$match': {'source_user': user}},
    {'$group': {
        '_id': {
            'source_user': '$source_user',
            'destination_user': '$destination_user'
        },
        'total': {'$sum': 1},
        'unread': {'$sum': { '$cond': [ '$is_read', 0, 1 ] }}
        }
    },
    {'$project': {
        'source_user': '$_id.source_user',
        'destination_user': '$_id.destination_user',
        'total': 1,
        'unread': 1,
        '_id': 0
    }}
])

MongoDB Playground

Upvotes: 1

Related Questions