Mongo - finding records with keys containing dots

Question

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:

db.collection.find({"data.field.with.dots" : { $exists : true }})
db.collection.find({"data.field\uff0ewith\uff0edots" : { $exists : true}})

So finally you'd like to get something like this: `{ "_id" : NumberLong(2761632), "data" : { "fieldwithdots" : { ... } } }` ? — mickl, Feb 12 '19 at 17:48
All I want is to find all the records of the form `{ "_id" : NumberLong(xxx), "data" : { "field.with.dots" : { ... } } }`. Once found I can update them to remove the dots or replace them with something else. — PGBI, Feb 12 '19 at 18:03

score 4 · Accepted Answer · answered Feb 12 '19 at 18:12

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

Mongo - finding records with keys containing dots

1 Answers1