CODEWITHSUNDEEP
arraysmongodbmongodb-queryaggregation-frameworkpymongo
Rifat Rakib
Rifat Rakib

Reputation: 134

How to sum the values from a field in subdocuments from an array?

How do I project only the words found in each document from any of the keywords provided? The structure of the document is as follows:

{
    _id: 24752893,
    dictionary: [
        {
            word: 'word1',
            count: 2,
        },
        {   
            word: 'word2',
            count: 5,
        },
        {
            word: 'word4',
            count: 1,
        },
        ....
    ]
},
{
    _id: 6786765789,
    dictionary: [
        {
            word: 'word4',
            count: 3,
        },
        {
            word: 'word2',
            count: 6,
        },
        {
            word: 'word3',
            count: 3,
        },
        {
            word: 'word5',
            count: 1,
        },
        ....
    ]
},
........
{
    _id: 76675567,
    dictionary: [
        {
            word: 'word1',
            count: 7,
        },
        {
            word: 'word3',
            count: 2,
        },
        ....
    ]
}

If a list of keywords like ['word2', 'word3'] is given, and a document should be retrieved whenever any of the words in the keyword list is found in it. I have written this aggregation pipeline to get the necessary documents:

client.database.collection.aggregate([
    {
    '$project': {
        '_id': 1,
        'dictionary': {
            '$filter': {
                'input': '$dictionary',
                'as': 'words',
                'cond': {
                    '$in': [
                        '$$words.word', keywords
                    ]
                }
            }
        },
    }
},
{
    '$match': {
        'dictionary': {
            '$ne': []
        }
    }
},
,
{
    '$unwind': '$dictionary'
},
{
    '$group': {
        '_id': '$_id',
        'score': {
            '$sum': '$dictionary.count'
        }
    }
}
])

What I want to do is instead of projecting the whole dictionary, I want to project only the matching words for each document along with its count. And of course, I want the dictionary for each document to be in separate projected documents. Is there any way to do this?

Upvotes: 1

Views: 169

Answers (1)

Dheemanth Bhat
Dheemanth Bhat

Reputation: 4452

Use $filter to filter your arrays, try this:

let keywords = ['word2', 'word3']

db.collection.aggregate([
    {
        $project: {
            _id: 0,
            dictionary: {
                $filter: {
                    input: "$dictionary",
                    as: "word",
                    cond: {
                        $in: ["$$word.word", keywords]
                    }
                }
            }
        }
    },
    {
        $match: {
            $expr: {
                $gt: [{ $size: "$dictionary" }, 0]
            }
        }
    }
]);

Output:

/* 1 */
{
    "dictionary" : [
        {
            "word" : "word2",
            "count" : 5
        }
    ]
},

/* 2 */
{
    "dictionary" : [
        {
            "word" : "word2",
            "count" : 6
        },
        {
            "word" : "word3",
            "count" : 3
        }
    ]
},

/* 3 */
{
    "dictionary" : [
        {
            "word" : "word3",
            "count" : 2
        }
    ]
}

Upvotes: 1

Related Questions