MongoDB dot (.) in key name

Question

It seems mongo does not allow insertion of keys with a dot (.) or dollar sign ($) however when I imported a JSON file that contained a dot in it using the mongoimport tool it worked fine. The driver is complaining about trying to insert that element.

This is what the document looks like in the database:

{
    "_id": {
        "$oid": "..."
    },
    "make": "saab",
    "models": {
        "9.7x": [
            2007,
            2008,
            2009,
            2010
        ]
    }
}

Am I doing this all wrong and should not be using hash maps like that with external data (i.e. the models) or can I escape the dot somehow? Maybe I am thinking too much Javascript-like.

Solution 1

MongoDB doesn't support keys with a dot in them so you're going to have to preprocess your JSON file to remove/replace them before importing it or you'll be setting yourself up for all sorts of problems.

There isn't a standard workaround to this issue, the best approach is too dependent upon the specifics of the situation. But I'd avoid any key encoder/decoder approach if possible as you'll continue to pay the inconvenience of that in perpetuity, where a JSON restructure would presumably be a one-time cost.

Solution 2

As mentioned in other answers MongoDB does not allow $ or . characters as map keys due to restrictions on field names. However, as mentioned in Dollar Sign Operator Escaping this restriction does not prevent you from inserting documents with such keys, it just prevents you from updating or querying them.

The problem of simply replacing . with [dot] or U+FF0E (as mentioned elsewhere on this page) is, what happens when the user legitimately wants to store the key [dot] or U+FF0E?

An approach that Fantom's afMorphia driver takes, is to use unicode escape sequences similar to that of Java, but ensuring the escape character is escaped first. In essence, the following string replacements are made (*):

\  -->  \\
$  -->  \u0024
.  -->  \u002e

A reverse replacement is made when map keys are subsequently read from MongoDB.

Or in Fantom code:

Str encodeKey(Str key) {
    return key.replace("\\", "\\\\").replace("\$", "\\u0024").replace(".", "\\u002e")
}

Str decodeKey(Str key) {
    return key.replace("\\u002e", ".").replace("\\u0024", "\$").replace("\\\\", "\\")
}

The only time a user needs to be aware of such conversions is when constructing queries for such keys.

Given it is common to store dotted.property.names in databases for configuration purposes I believe this approach is preferable to simply banning all such map keys.

(*) afMorphia actually performs full / proper unicode escaping rules as mentioned in Unicode escape syntax in Java but the described replacement sequence works just as well.

Solution 3

The Mongo docs suggest replacing illegal characters such as $ and . with their unicode equivalents.

In these situations, keys will need to substitute the reserved $ and . characters. Any character is sufficient, but consider using the Unicode full width equivalents: U+FF04 (i.e. “＄”) and U+FF0E (i.e. “．”).

Solution 4

The latest stable version (v3.6.1) of the MongoDB does support dots (.) in the keys or field names now.

Field names can contain dots (.) and dollar ($) characters now

Solution 5

A solution I just implemented that I'm really happy with involves splitting the key name and value into two separate fields. This way, I can keep the characters exactly the same, and not worry about any of those parsing nightmares. The doc would look like:

{
    ...
    keyName: "domain.com",
    keyValue: "unregistered",
    ...
}

You can still query this easy enough, just by doing a find on the fields keyName and keyValue.

So instead of:

 db.collection.find({"domain.com":"unregistered"})

which wouldn't actually work as expected, you would run:

db.collection.find({keyName:"domain.com", keyValue:"unregistered"})

and it will return the expected document.

View more solutions

Comments

[Sam Denty]

Worth looking at, npmjs.com/package/mongo-escape

[Michael Yagudaev]

Yeah I am aware of that, I read the documentation and the driver implementation for Node.js. I was just wondering what is the standard way to dealing with this. Because I would want to search based on those values too. Should I just build and encoder and decoder function to replace a . with [dot]? Or should I restructure my JSON somehow to avoid it all together?

[JohnnyHK]

I don't think there's a standard way, the best approach is too dependent upon the specifics of the situation. But I'd avoid any key encoder/decoder approach if possible as you'll continue to pay the inconvenience of that in perpetuity, where a JSON restructure would presumably be a one-time cost.

[Michael Yagudaev]

alright thanks for the advice, you should incorporate that into the answer and I will mark it as the answer to this question.

[JohnnyHK]

@yagudaev Okay, I added that into the answer.

[prototype]

Ran into this situation again. This seems to occur not so much with app key names, which we can control and often need to query on, but with user supplied data in nested data structures, which we can't control, but (a) would like to store in Mongo, (b) we know which specific fields this might happen in (e.g. models here), and (c) we don;t need query them by key name in Mongo. So a pattern that I settled on is to JSON.stringify this field on save, and 'JSON.parse` on retrieve.

[Tzury Bar Yochay]

If you must, you can provide {check_keys: false} option to bypass this issue.

[Michael Yagudaev]

I like this, clean solution with 1-way hashing and really similar to the way things work under the hood.

[nobody]

That sounds like a recipe for massive debugging headaches down the road.

[prototype]

@TzuryBarYochay OMG you've found the MongoDB equivalent of the northwest passage. I think this should be the accepted answer.

[Steve Eynon]

The problem with using hashes as keys, is that they're not not guaranteed to be unique, and they frequently produce collisions. Plus computing a cryptographic hash every time you want to access a map doesn't seem like the most optimal solution to me.

[B Seven]

Why is this better than replacing the period with a special character or sequence?

[emarel]

@TzuryBarYochay where do you add the {check_keys: false}?

[Tzury Bar Yochay]

@emarel db.collection_foo.update({this: "that"}, {$set: {a:"b"}}, {check_keys: false})

[Jeffrey A. Gochin]

I just encountered this issue this week. But, it wasn't mongo that was throwing the exception it was NodeJS. (Upon which MongoDb is built) JSON.parse is what throws the error. I feel like this is a fairly recent development, and wonder why NodeJS (or is it ES6) that does not like the period. In any case I just took using Array instead of "hash" tables built from JavaScript objects.

[Myer]

@AndrewMedico, @tamlyn - I think the docs mean something like db.test.insert({"field\uff0ename": "test"})

[cw']

Should use //g to replace all occurrences and not just the first. Also, using the full-width equivalents as in Martin Konecny's answer seems to be a good idea. Finally, one backslash is enough for the encoding. key.replace(/\./g, '\uff0e').replace(/\$/g, '\uff04').replace(/\\/g, '\uff3c')

[Steve Eynon]

@cw' - The code is in a Java like syntax, so replace does actually replace all occurrences, and double backslashes are required for escaping backslashes. And again, you need to introduce some form of escaping to ensure all cases are covered. Someone, at some time, may actually want a a key of U+FF04.

[B T]

-1 A. That's a terrible idea - what if someone is actually trying to use those unicode characters as a key? Then you have a silent error that will do who knows what to your system. Don't use ambiguous escape methods like that. B. the mongo docs no longer say that, probably because someone realized its a terrible idea

[B T]

And if someone uses a _ in any of their keys, you'll get bugs.

[Sergio Tulentsev]

@BT: the docs still say that, just not at that page. docs.mongodb.com/v3.0/faq/developers/…. But I agree, it's bad idea. :)

[B T]

@SergioTulentsev I got them to remove the recommendation : ) github.com/mongodb/docs/commit/…

[Sergio Tulentsev]

@BT: hat tip to you, sir :)

[PeterD]

Tzury Bar Yochay, can {check_keys: false} option be used in C# driver? I could not find anything...

[jvc]

This escaping can still break, if you got strings like '.~p.'. Here the escaped string will be '~p~~p~p'. Unescaping will give you '.~..', which is different from the actual string.

[B T]

@jvc You're right! I've fixed the explanation and example escape functions. Let me know if they're still broken!

[profiler]

How You did it? Could You please help me with that same case.

[Steve]

I added a query example. Does that help?

[JMax]

Even if the server supports it now, the driver still check for $ and dots in keys and do not accept them. Therefore Mongo only theoretically supports dots and dollar characters. Practically this is not yet usable :(

[h4ck3d]

Maybe you're using some old or incompatible client. I've been using this on my production servers without any sweat. I've checked for NodeJS and Java clients.

[JMax]

With Java it definetly does not work! Try following command: mongoClient.getDatabase("mydb").getCollection("test").insert‌​One(new Document("value", new Document("key.with.dots", "value").append("$dollar", "value"))); It fails using mongodb-driver.3.6.3 and MongoDB 3.6.3.

[Learning is a mess]

Indeed, I just tried with a setup mongodb-4.1.1 and pymongo-3.7.1. I can add documents containing keys with . with robomongo but not from pymongo, it sill raises InvalidDocument: key '1.1' must not contain '.' Wish it had been fixed by now...

[xyzt]

I tried with mongodb server 4.0.9 and java driver 3.10.2 but it doesn't accept dot in key name. it's strange that when try that using robomongo it works...

[Zen]

Converting strings to base64 is much better.

[Abhidemon]

As it turns out, Mongodb Supports dots and dollars in it's latest versions. See:- stackoverflow.com/a/57106679/3515086

[hello_abhishek]

This is the best answer here. :+1

[JeremyDouglass]

3.6 can store them, yes, but it is not yet supported, may throw driver errors, and may break query/updates: restrictions: "The MongoDB Query Language cannot always meaningfully express queries over documents whose field names contain these characters (see SERVER-30575). Until support is added in the query language, the use of $ and . in field names is not recommended and is not supported by the official MongoDB drivers."

[Sathya Narayan C]

required a bean of type 'org.springframework.data.mongodb.core.convert.MappingMongoC‌​onverter' that could not be found.

[Moonlit]

Why \$ and not just $ though?

[Steve Eynon]

Hi @Moonlit, good question. It is because the example is written in Fantom and the $ character is reserved for String Interpolation so it needs to be escaped with a backslash. So in effect, yes, IT IS just replacing "$".