Querying field with with dot in key

MongoDB University M312: Diagnostics and Debugging
#1

Hello,

The documentation says:

starting in MongoDB 3.6, the server permits storage of field names that contain dots (i.e. . ) and dollar signs (i.e. $ ).

IMPORTANT

The MongoDB Query Language cannot always meaningfully express queries over documents whose field names contain these characters

But the profile data seems to contain a field with a dot (address.zip key):

{
        "_id" : ObjectId("5c461eb54a688dcf3cec024b"),
        "op" : "query",
        "ns" : "m312.employees",
        "query" : {
                "find" : "employees",
                "filter" : {
                        "address.zip" : "21019"
                }
        },

Can you please point to the documentation that explains how to query on fields with dots in their keys?

Best Regards,
Andrew

#2

@ andrewnessinjim

I’m afraid you have not understood how the MongoDB query language works. Please review the documentation here and here to see how the dot notation is used. That should answer your question,

#3

@andrewnessinjim

I encountered the same problem in the past. Contrary to what DHz noted, you are using the proper syntax, but you are bumping up a MongoDB limitation.

It absolutely is possible to have a field name with a dot in it, as shown below:

rs0:PRIMARY> db.test.find();
{ "_id" : ObjectId("5c47c74cb4e35a0c30421fd2"), "name" : "Tom", "address.zip" : 90210 }

The above can be queried by “name” but not by “address.zip”. The problem is that whenever a dot appears in the query, MongoDB assumes that the leftmost portion is the name of a hash and the rightmost portion is a key in the hash. Essentially, you are only searching on a document with the schema:

{ address: { zip: "21019" }

And that is not the shape of your document, so there are no results. MongoDB does not complain when adding field names that include . or $ characters, but they are not supported. In practice, you can store the data with no problem but will run into problems querying the data.

The common workaround is to avoid using those characters and use the unicode character equivalent, which makes the field name look like it contains a . or $, but actually it’s a different character code that MongoDB will not treat differently than other text.

A more cringeworthy way would be to not filter on the field at all (possibly returning all documents) and filter client-side. There are obvious performance implications, but it is an option.

If you really wanted to make it work, you can probably store a function in MongoDB. That function can then be used as part of a map-reduce aggregation component. Since you are writing the JavaScript code in the server-side function, you can have it check for a field name with a dot in it easily enough. I am sure that running custom JavaScript would pretty much nullify MongoDB’s automatic optimizations, but if you needed to do a query against a large data set from a slow client, keeping all of the processing and data server-side would make sense.

In summary, don’t use field names with . or $ in them unless you are looking for a fun challenge. :slight_smile:

#4

@ ciscofu

Good and very complete answer, but the issue that @ andrewnessinjim is running into is that he really has not reviewed the schema of the documents here. There is no field ‘address.zip’ in the document; the address is an embedded document and the query is exactly what you would normally expect for an embedded document. My fear here is that he will zoom down the wrong path trying to access a field ‘address.zip’ and never look at the obvious issue. :wink:

#5

Thanks a lot for your reply guys! Guess I was not really clear on my question. I know using dot for a field name is a bad idea, and the documents in the actual collection don’t have any key names with a dot.

My issue is that profiler data provided for the assignment consists of a key name with a dot. These are still documents and are meant to be queried. And these are generated by the MongoDB profiler and has nothing to do with the user schema. I was trying to find all the entries in the provided profiler dump, where the query filter is against “address.zip”. But as it stands, I don’t see any way to query the profiler dump in such a manner because of that dot in that key name. Since MongoDB generated that dump, I was expecting documentation on how to query on such fields.

I will explain with examples if that’s not clear, once my computer is accessible.

Best Regards,
Andrew

#6

@ andrewnessinjim

That helps a lot. So I’m guessing here that you’re trying to query the profile data in the Chapter 3 Lab: Analyze Profiler Data, correct?

In that case – and since this Lab is closed – I would suggest that you can simply use a standard regex expression to query for what you want. For example, a quick review of the schema of the profiler_data collection shows the field “ns” which will always be of the form “db.collection”, correct? So to scan for a specific “ns” you can use

db.profiler_data.find( { "ns": /m312\.employees/ } )

And so on. So that covers your general question I think. If this is specific to the profiler_data file however, notice that the address.zip value is buried fairly deep into your schema. FWIW. Good luck.