0

Consider such a collection:

> db.test.find({})
{ "_id" : ObjectId("5f969419d40c1580f2d4aa31"), "users" : { "foo@bar.com" : "baz" } }
{ "_id" : ObjectId("5f9694d4d40c1580f2d4aa33"), "users" : { "baz@test.com" : "foo" } }

I want to find documents where users contains field foo@bar.com. Firstly I tried

> db.test.find({"users.foo@bar.com": { $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?

MrLoon
  • 831
  • 1
  • 8
  • 16
  • 1
    You can use an aggregation query, With `$objectToArray` operator, you can match the string `"foo@bar.com"`. – 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

2 Answers2

1

You can do it using aggregation. Try this query.

db.test.aggregate([
  {
    "$project": {
      "users": {
        "$objectToArray": "$users"
      }
    }
  },
  {
    "$match": {
      "users.k": "foo@bar.com"
    }
  },
  {
    "$project": {
      "users": {
        "$arrayToObject": "$users"
      }
    }
  }
])

Here is Mongo Playground

wak786
  • 1,562
  • 1
  • 8
  • 12
0

Try this one:

db.test.find({$expr:{$literal:{"users.foo@bar.com":{$exists:true}}}})

Explanation

  • $expr allows us to use aggregation operators in MQL
  • $literal returns "users.foo@bar.com" as a field (no parsing)
  • $literal does not touch the inner level $exists still works.
Minsky
  • 2,277
  • 10
  • 19