Mongodb Dot (.) in Key Name

MongoDB dot (.) in key name

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.

Mongo - finding records with keys containing dots

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

Can I use the dot character (.) for a field name in Mongo DB?

While it is stated in the docs that MongoDB(version 3.6 upwards) works well with field names containing dots(.), it is not recommended:

IMPORTANT

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.

Source

Notice it also stated that using field names containing dots(.) is not supported by official MongoDB drivers, one of which is what you are using, most likely this is why you are having error on DB insertion.

Insert field name with dot in mongo document

From this link, How to use dot in field name?

You can replace dot symbols of your field name to Unicode equivalent
"\uff0E":

Update: As Fred suggested, please use "\u002E" for "."

Mongodb replacing dot (.) in key name while inserting document

If it isn't possible to use keys with . in Mongodb, you'll have to modify the input data :

hash = {
  'key.1' => {
    'second.key' => {
      'third.key' => 'val.1',
      'fourth.key' => ['val.1', 'val.2']
    }
  }
}

Transforming string keys

This recursive method transforms the keys of a nested Hash :

def nested_gsub(object, pattern = '.', replace = '_')
  if object.is_a? Hash
    object.map do |k, v|
      [k.to_s.gsub(pattern, replace), nested_gsub(v, pattern, replace)]
    end.to_h
  else
    object
  end
end

nested_gsub(hash) returns :

{
    "key_1" => {
        "second_key" => {
             "third_key" => "val.1",
            "fourth_key" => [
                "val.1",
                "val.2"
            ]
        }
    }
}

Transforming keys and values

It's possible to add more cases to the previous method :

def nested_gsub(object, pattern = '.', replace = '_')
  case object
  when Hash
    object.map do |k, v|
      [k.to_s.gsub(pattern, replace), nested_gsub(v, pattern, replace)]
    end.to_h
  when Array
    object.map { |v| nested_gsub(v, pattern, replace) }
  when String
    object.gsub(pattern, replace)
  else
    object
  end
end

nested_gsub will now iterate on string values and arrays :

{
    "key_1" => {
        "second_key" => {
             "third_key" => "val_1",
            "fourth_key" => [
                "val_1",
                "val_2"
            ]
        }
    }
}

MongoDB query with special characters in key

With the following workaround I was able to directly access documents by their keys, even though they have a dot in their key:

db.getCollection('mycollection').aggregate([
{$match: {mymapfield: {$type: "object" }}}, //filter objects with right field type
{$project: {mymapfield: { $objectToArray: "$mymapfield" }}}, //"unwind" map to array of {k: key, v: value} objects
{$match: {mymapfield: {k: "my.key.with.dot", v: "myvalue"}}} //query
])

MongoDB Java nested documents not accessible using dots in key name

After spending some time reading documentation and other Stack
Overflow questions, I learned that using dots in the key name (like
one.two for the key) should work, but it isn't for me.

The dot-notation works fine when used within a find method's query filter. For example,

Document document = collection.find(Filters.eq("one.two", "three")).first();
System.out.println(document);    // prints the returned document

or its mongo shell equivalent:

db.collection.find( { "one.two": "three" } )

The Document class's get() method takes an Object (a String key) as a parameter and returns an Object.

Consider the code:

Document doc = coll.find(eq("data-id", "1234")).first();
System.out.println(doc);

The output Document{{_id=1.0, data-id=1234, one=Document{{two=three}}}} shows there are three keys: _id, data-id and one. Note there is no key named as one.two. The key two is within the sub-dcument of the document with the key one.

So, from your code:

document.get("one.two");    // This is null ((Document)
document.get("one")).get("two"); // Not null

The first statement returns null, and the next one returns three (the String value). Both are correct results and that is the behavior of the Documentclass API.

You should use the method getEmbedded to access the embedded field one.two. So, replace document.get("one.two") with

document.getEmbedded(Arrays.asList("one", "two"), String.class)

The result is "three", as expected.

---

Related Topics