What is incorrect in this query to find the no of records with a particular value

db.inspections.find({ “address”: {
“city”: “NEW YORK”}}).count()

What’s wrong is your find doesn’t work.
Try:

db.inspections.find({ “address.city”: “NEW YORK”}).count()

I was surprised to read

because I was sure the query object notation {address:{city:NY}} rather than dot notation {address.city:NY} was correct.

So I check with mongosh:

mongosh> c.find()
{ _id: 0, address: { city: 'NEW YORK' } }

// object notation version:
  mongosh> c.find({ "address" : { "city" : "NEW YORK" } })
{ _id: 0, address: { city: 'NEW YORK' } }

// dot notation version:
mongosh> c.find({ "address.city" : "NEW YORK" })
{ _id: 0, address: { city: 'NEW YORK' } }

So both work as I was expecting. I would really be unhappy if I had to do things like:

c.find( { "address.city" : "NEW YORK" , "address.state" : "NY" } )

rather than

c.find( { "address" : { "city" : "NEW YORK" , "state" : "NY" } )

So, @Pavan_Tipparaju, your query is supposed to be correct. If you got a count of 0 then most likely you are not connected to the appropriate server, or your are not using the appropriate database, or the collection has been altered or deleted.

Yes, I would have thought so. But I tried a similar example and couldn’t make it work, whereas the dot-notation did work. Maybe it was just late at night, @steevej :slight_smile:

Given your credentials, this is definitively the case. B-)

Maybe I still am asleep, @steevej :slight_smile:

Here’s what I get in Atlas. What am I doing wrong?

Atlas Cluster0-shard-0 [primary] jwoehr> db.test.find()
[
  {
    _id: ObjectId("624a700b51740645747b6858"),
    title: 'Foo zotz',
    author: 'Superman',
    reviews: { foo: 123, arf: 200 }
  }
]
Atlas Cluster0-shard-0 [primary] jwoehr> db.test.find({"reviews": {"foo": 123}})

Atlas Cluster0-shard-0 [primary] jwoehr> db.test.find({"reviews.foo": 123})
[
  {
    _id: ObjectId("624a700b51740645747b6858"),
    title: 'Foo zotz',
    author: 'Superman',
    reviews: { foo: 123, arf: 200 }
  }
]


Atlas Cluster0-shard-0 [primary] jwoehr> 

No, you are not, but I was.

{ "reviews" : object } means strict equality. So the query will only match documents that have the field reviews exactly equals to the object, all the same fields and all the same values in the same order.

Ooops

saru mo ki kara ochiru

So you were right and the query {address:{city:NY}} was wrong because address has other fields.

// starting with
[
  { _id: 0, a: { b: 1 } },
  { _id: 1, a: { b: 1, c: 2 } }
]

mongosh> c.find( { a : { b:1 } } )
[ { _id: 0, a: { b: 1 } } ]


mongosh> c.find( { a : { b:1 , c:2} } )
[ { _id: 1, a: { b: 1, c: 2 } } ]

mongosh> c.find( { a : { c:2 , b:1} } )
// no result because object {c:2,b:1} is not equals to object {b:1,c:2} just like direct JS

mongosh> c.find( { "a.c" : 2 , "a.b" : 1 } )
[ { _id: 1, a: { b: 1, c: 2 } } ]

And I actually knew that: Any drawbacks in having _id an object - #5 by steevej

Ditto with arrays Problem matching values in array - #4 by steevej

So I was asleep, now I am awake, … I think.

1 Like

Thanks for clarifying, @steevej, TIL some more MongoDB.

1 Like