1 Question: How to project / group on a field name that starts with dollar using aggregation

Question created on Sunday February 26, 2017

Specifically in system.profile -> updateobj.$set

Given a document of the form:

db.system.profile.findOne({op: "update", ns: "zz.top", upsert: true})
{
    "_id" : ObjectId("589c6bf4be7acf4c5b8a4cb4"),
    "op" : "update",
    "ns" : "zz.top",
    "query" : {
            "ts" : ISODate("2017-02-07T16:08:01Z"),
            "user" : 22464
    },
    "updateobj" : {
            "$set" : {
                    "version" : "2.0.4",
                   ...
            }
    }
    ...
}

I want to find (a simplified case for my problem) all documents with duplicate versions.

Something such as:

db.system.profile.aggregate(
    {$match: {op: "update", ns: "zz.top", upsert: true}}, 
    {$group: {_id : {uid : "$updateobj.$set.version"}, count: {$sum : 1}}},
    {$match: {count : {$gt : 1}}}
)

or:

db.system.profile.aggregate(
    {$match: {op: "update", ns: "zz.top", upsert: true}},
    {$project: {"v" : "$updateobj.$set.version"}},
    {$group: {_id : "$v", count: {$sum : 1}}},
    {$match: {count : {$gt : 1}}}
) 

Awkwardly, I've been struggling.

3
1 answer                              3                         

Unfortunately, currently this is not possible.

To query this, you need to read out of the profile collection, sanitize the dollars signs to something else and then save them to another collection.

Here is a corresponding bug opened in 2013: https://jira.mongodb.org/browse/SERVER-8436

2
Sunday February 26, 2017
source posted here