MongoDB aggregate nested array correctly
<p>OK I am very new to Mongo, and I am already stuck.</p>

<p>Db has the following structure (much simplified for sure): </p>

<pre><code>{
    {
        "_id" : ObjectId("57fdfbc12dc30a46507044ec"),

        "keyterms" : [ 
            {
                "score" : "2",
                "value" : "AA",
            }, 
            {
                "score" : "2",
                "value" : "AA",
            }, 
            {
                "score" : "4",
                "value" : "BB",
            },
            {
                "score" : "3",
                "value" : "CC",
            }
        ]
    },

    {
        "_id" : ObjectId("57fdfbc12dc30a46507044ef"),

        "keyterms" : [ 
        ...
</code></pre>

<p>There are some Objects. Each Object have an array "keywords". Each of this Arrays Entries, which have score and value. There are some duplicates though (not really, since in the real db the keywords entries have much more fields, but concerning value and score they are duplicates).</p>

<p>Now I need a query, which</p>

<ul>
<li>selects one object by id</li>
<li>groups its keyterms in by value</li>
<li>and counts the dublicates</li>
<li>sorts them by score</li>
</ul>

<p>So I want to have something like that as result</p>

<pre><code>// for Object 57fdfbc12dc30a46507044ec
"keyterms"; [
    {
        "score" : "4",
        "value" : "BB",
        "count" : 1
    },


    {
        "score" : "3",
        "value" : "CC",
        "count" : 1
    }

    {
        "score" : "2",
        "value" : "AA",
        "count" : 2
    }

]
</code></pre>

<p>In SQL I would have written something like this</p>

<pre><code>select 
    score, value, count(*) as count
from
    all_keywords_table_or_some_join
group by
    value
order by
    score
</code></pre>

<p>But, sadly enough, it's not SQL.</p>

<p>In Mongo I managed to write this:</p>

<pre><code>db.getCollection('tests').aggregate([
    {$match: {'_id': ObjectId('57fdfbc12dc30a46507044ec')}},
    {$unwind: "$keyterms"}, 
    {$sort: {"keyterms.score": -1}}, 
    {$group: {
        '_id': "$_id", 
        'keyterms': {$push: "$keyterms"}
    }},
    {$project: {
        'keyterms.score': 1,
        'keyterms.value': 1
    }}
])
</code></pre>

<p>But there is something missing: the grouping of the the keywords by their value. I can not get rid of the feeling, that this is the wrong approach at all. How can I select the keywords array and continue with that, and use an aggregate function inly on this - that would be easy.</p>

<p>BTW I read this
(<a href="https://stackoverflow.com/questions/22424059/mongo-aggregate-nested-array">Mongo aggregate nested array</a>)
but I can't figure it out for my example unfortunately...</p>