Support for dots in field names

Unlike MongoDB, which does not allow dots, ( . ), in JSON or BSON field names, HCL OneDB™ conforms to the JSON standard and allows dots. For example: {"user.fn" : "Jake"}. However, you cannot run a query or an operation directly on a field that has a dot in its name. In queries, a dot in between field names indicates a hierarchy.

Here the rules of using field names with dots in them with HCL OneDB:

  • You can insert a document that has a field name with a dot in it. You do not get an error.
  • You cannot use a field name with a dot in it in a query or operation. HCL OneDB ignores the field. The query does not return the matching document. The operation does not affect the value of the field.
  • You can return a document that includes a field name with a dot in it by querying on a field name in the same document that does not have a dot in it.

Allowing dots in field names is useful when you do not have control over the field names because your data comes from external sources, for example, the Google API. You still want to store those documents in your database, even though some fields might have dots in their names.

The following examples to illustrate how dots in field names work in HCL OneDB. The table name is tab1 and the column that contains JSON data is named data.

Suppose that you have the following document:

 {user : {fn : "Bob", ln : "Smith"}, "user.fn" : "Jake"}

You run the following statement to update a field:

SELECT data::json FROM tab1 WHERE BSON_UPDATE(data, '$set : {"user.fn" :
      "John:}}');

The following document is returned:

{user : {fn : "John", ln : "Smith"}, "user.fn" : "Jake"}

The value of the fn field that is in a subdocument to the user field is updated. The value of the user.fn field is not updated, but the value is returned. You cannot update the value of a field with a dot in its name, but you can retrieve the value.

Suppose that you have the following document:

{"user.firstname" : "Jake"}

You run this query to return the value of the user.firstname field:

SELECT data::json FROM tab1 WHERE BSON_KEYS_EXIST(data,
      "user.firstname");

No documents are returned.

If you have documents where all the fields have dots in their names, you must run a query to return all documents in the database to see them: for example:

SELECT data::json FROM tab1;