CODEWITHSUNDEEP
mongodbmongodb-query
PGBI
PGBI

Reputation: 710

Mongo - finding records with keys containing dots

Mongo does not allow documents to have dots in their keys (see MongoDB dot (.) in key name or https://softwareengineering.stackexchange.com/questions/286922/inserting-json-document-with-in-key-to-mongodb ).

However we have a huge mongo database where some documents do contain dots in their keys. These documents are of the form:

{
    "_id" : NumberLong(2761632),
    "data" : {
        "field.with.dots" : { ... }
    }
}

I don't know how these records got inserted. I suspect that we must have had the check_keys mongod option set to false at some point.

My goal is to find the offending documents, to update them and remove the dots. I haven't found how to perform the search query. Here is what I tried so far:

Upvotes: 4

Views: 2283

Answers (1)

mickl
mickl

Reputation: 49945

You can use $objectToArray to get your data in form of keys and values. Then you can use $filter with $indexOfBytes to check if there are any keys with . inside of it . In the next step you can use $size to filter out those documents where remaining array is empty (no fields with dots), try:

db.col.aggregate([
    {
        $addFields: {
            dataKv: {
                $filter: {
                    input: { $objectToArray: "$data" },
                    cond: {
                        $ne: [ { $indexOfBytes: [ "$$this.k", "." ] } , -1 ]
                    }
                }
            }
        }
    },
    {
        $match: {
            $expr: {
                $ne: [ { $size: "$dataKv" }, 0 ]
            }
        }
    },
    {
        $project: {
            dataKv: 0
        }
    }
])

Mongo playground

Upvotes: 4

Related Questions