Issue with a field name begining with a $ sign

I have the following data generated by mongodb in the collection system.profile, so i cannot decide to remove the $ sign from the field name $comment.

use testDollar;
db.col.drop();
db.col.insertOne({
    command: {
        q: {
            $comment: "some text",
            other: "other text",
        }
    }
});


I want to extract the $comment from the data. (Spoiler: I can’t do it.)

db.col.aggregate([
    { $addFields: {
        addedField: "$command.q.$comment",
    }},
    { $project: {
        _id: false,
        addedField: true,
    }},
]);

==> It doesn’t work. Result: "Invalid $addFields :: caused by :: FieldPath field names may not start with '$'. Consider using $getField or $setField."



It works if the field doesn’t start with $ sign.

db.col.aggregate([
    { $addFields: {
        addedField: "$command.q.other",
    }},
    { $project: {
        _id: false,
        addedField: true,
    }},
]);

==> It works. Result: { "addedField" : "other text" }



I try with $getField, as suggested in the error message.

db.col.aggregate([
    { $addFields: {
        addedField: {
            $getField: "command.q.$comment",
        },
    }},
    { $project: {
        _id: false,
        addedField: true,
    }},
]);

==> It doesn’t work. Result: { }



I try again with $getField and another syntax.

db.col.aggregate([
    { $addFields: {
        addedField: {
            $getField: "$command.q.$comment",
        },
    }},
    { $project: {
        _id: false,
        addedField: true,
    }},
]);

==> It doesn’t work. Result: "Invalid $addFields :: caused by :: FieldPath field names may not start with '$'. Consider using $getField or $setField."



I try again with $getField and another syntax.

db.col.aggregate([
    { $addFields: {
        addedField: {
            $getField: {
                field: "$comment",
                input: "$command.q",
            }
        },
    }},
    { $project: {
        _id: false,
        addedField: true,
    }},
]);

==> It doesn’t work. Result: "Invalid $addFields :: caused by :: '$CURRENT.comment' is a field path reference which is not allowed in this context. Did you mean {$literal: '$CURRENT.comment'}?"



I try the same query without the $ sign.

db.col.aggregate([
    { $addFields: {
        addedField: {
            $getField: {
                field: "other",
                input: "$command.q",
            }
        },
    }},
    { $project: {
        _id: false,
        addedField: true,
    }},
]);

==> It works. Result: { "addedField" : "other text" }



I try again with $getField and $literal as explained in $getField documentation: (If field begins with a dollar sign ( $ ), place the field name inside of a $literal expression to return its value.)

db.col.aggregate([
    { $addFields: {
        addedField: {
            $getField: {
                field: { literal: "$comment" },
                input: "$command.q",
            }
        },
    }},
    { $project: {
        _id: false,
        addedField: true,
    }},
]);

==> It doesn’t work. Result: "Invalid $addFields :: caused by :: $getField requires 'field' to evaluate to a constant, but got a non-constant argument"



So how can I do it?

Oh I found this but it’s pretty ugly. I hope the failure with $getField is only a syntax issue with a simple solution. If a field name with a $ can be inserted, there must be a straight way to retrieve it?

db.col.aggregate([
    { $addFields: {
        addedField: 
            { $getField: {
                field: "v",
                input: { $arrayElemAt: [ { $objectToArray: "$command.q" }, 0 ] }
            }}
    }},
    { $project: {
        _id: false,
        addedField: true,
    }},
]);

Hi @Joel_J,

Actually you were pretty close to the answer with the last attempt. There was just a missing $ sign on the literal syntax.

This should have given you the result you wanted :

db.col.aggregate([
    {"$addFields": {
         "addedField":{
             "$getField":{
                 "field":{"$literal":"$comment"}, 
                 "input":"$command.q"
             }
         }
    }
}])

The default level for $getField is on the top level document $$CURRENT. Here we defined the level explicitly to be the nested level (sub-document) field command.q.

Regards,
Wan.

2 Likes

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.