Query nested doc's field with dot in the name

Question

Consider such a collection:

> db.test.find({})
{ "_id" : ObjectId("5f969419d40c1580f2d4aa31"), "users" : { "[email protected]" : "baz" } }
{ "_id" : ObjectId("5f9694d4d40c1580f2d4aa33"), "users" : { "[email protected]" : "foo" } }

I want to find documents where users contains field [email protected]. Firstly I tried

> db.test.find({"[email protected]": { $exists: true } })

But it returned nothing. Because of the dot (.) in field's name it was looking for a such a field: users > foo@bar > com which does not exist. I learned that the dot in key's name can be escaped with \u002e so I tried

> db.test.find({"users.foo@bar\u002ecom": { $exists: true } })

But it also returns nothing. I guess that I am not escaping properly. How should I do this?

You can use an aggregation query, With `$objectToArray` operator, you can match the string `"[email protected]"`. — prasad_, Oct 26 '20 at 10:16
Related question: [mongodb - How to use dot in field name? - Stack Overflow](https://stackoverflow.com/questions/8429318/how-to-use-dot-in-field-name) — user202729, May 30 '22 at 02:54

score 1 · Answer 1 · answered Oct 26 '20 at 11:17

You can do it using aggregation. Try this query.

db.test.aggregate([
  {
    "$project": {
      "users": {
        "$objectToArray": "$users"
      }
    }
  },
  {
    "$match": {
      "users.k": "[email protected]"
    }
  },
  {
    "$project": {
      "users": {
        "$arrayToObject": "$users"
      }
    }
  }
])

Here is Mongo Playground

Minsky · Answer 2 · 2020-10-26T10:23:17.967

0

Try this one:

db.test.find({$expr:{$literal:{"[email protected]":{$exists:true}}}})

Explanation

$expr allows us to use aggregation operators in MQL
$literal returns "[email protected]" as a field (no parsing)
$literal does not touch the inner level $exists still works.

edited Oct 26 '20 at 10:23

answered Oct 26 '20 at 10:18

Minsky

2,277
10
19

Query nested doc's field with dot in the name

2 Answers2