MongoDB query for null

Question

I have a map reduced data set of stats on our site's sources of traffic. It is hosted at mongohq.com, I have the mongo client installed locally and am connecting to the mongohq db via the local client.

If I query for db.page_views_stats.find({"_id.offer":"chihuahua-insurance"})

I get 21 rows:

{ "_id" : { "month" : ISODate("2013-04-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 30 } }
{ "_id" : { "month" : ISODate("2013-04-01T00:00:00Z"), "source" : "none", "offer" : "chihuahua-insurance" }, "value" : { "count" : 2 } }
{ "_id" : { "month" : ISODate("2013-05-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 5 } }
{ "_id" : { "month" : ISODate("2013-05-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 495 } }
{ "_id" : { "month" : ISODate("2013-05-01T00:00:00Z"), "source" : "email", "offer" : "chihuahua-insurance" }, "value" : { "count" : 8 } }
{ "_id" : { "month" : ISODate("2013-05-01T00:00:00Z"), "source" : "none", "offer" : "chihuahua-insurance" }, "value" : { "count" : 68 } }
{ "_id" : { "month" : ISODate("2013-06-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 40 } }
{ "_id" : { "month" : ISODate("2013-06-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 63 } }
{ "_id" : { "month" : ISODate("2013-06-01T00:00:00Z"), "source" : "email", "offer" : "chihuahua-insurance" }, "value" : { "count" : 1 } }
{ "_id" : { "month" : ISODate("2013-07-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 110 } }
{ "_id" : { "month" : ISODate("2013-07-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 48 } }
{ "_id" : { "month" : ISODate("2013-08-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 219 } }
{ "_id" : { "month" : ISODate("2013-08-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 1 } }
{ "_id" : { "month" : ISODate("2013-08-01T00:00:00Z"), "source" : "tweet", "offer" : "chihuahua-insurance" }, "value" : { "count" : 1 } }
{ "_id" : { "month" : ISODate("2013-09-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 65 } }
{ "_id" : { "month" : ISODate("2013-10-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 48 } }
{ "_id" : { "month" : ISODate("2013-11-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 67 } }
{ "_id" : { "month" : ISODate("2013-11-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 1 } }
{ "_id" : { "month" : ISODate("2013-12-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 56 } }
{ "_id" : { "month" : ISODate("2013-12-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 6 } }
{ "_id" : { "month" : ISODate("2014-01-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 7 } }

They all have a source field some of which are null

If I query for any of:

db.page_views_stats.find({$and:[{"_id.source": null},{"_id.offer":"chihuahua-insurance"}]})
db.page_views_stats.find({$and:[{"_id.source":{ $exists: false } },{"_id.offer":"chihuahua-insurance"}]})
db.page_views_stats.find({$and:[{"_id.source":{ $type: 10 } },{"_id.offer":"chihuahua-insurance"}]})
db.page_views_stats.find({"_id.source":{ $type: 10 },"_id.offer":"chihuahua-insurance"})
db.page_views_stats.find({"_id.source": null,"_id.offer":"chihuahua-insurance"})
db.page_views_stats.find({"_id.source":{ $exists: false },"_id.offer":"chihuahua-insurance"})

I get 0 results returned.

How do I find the results where source is null?

If you send a ticket to [email protected], we'll have a look to see what's going on with your DB. That behavior doesn't seem quite right. — MrKurt
– MrKurt, Commented Jan 30, 2014 at 22:05

Derick · Accepted Answer · 2014-01-07 10:17:46Z

I have just imported your 21 rows into a collection, and then I query them with:

db.page_views_stats.find({"_id.source": null,"_id.offer":"chihuahua-insurance"})

This is the prefered query, and the answer I get back works too. See the shell session:

> db.page_views_stats.drop();
true

> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-04-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 30 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-04-01T00:00:00Z"), "source" : "none", "offer" : "chihuahua-insurance" }, "value" : { "count" : 2 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-05-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 5 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-05-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 495 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-05-01T00:00:00Z"), "source" : "email", "offer" : "chihuahua-insurance" }, "value" : { "count" : 8 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-05-01T00:00:00Z"), "source" : "none", "offer" : "chihuahua-insurance" }, "value" : { "count" : 68 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-06-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 40 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-06-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 63 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-06-01T00:00:00Z"), "source" : "email", "offer" : "chihuahua-insurance" }, "value" : { "count" : 1 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-07-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 110 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-07-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 48 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-08-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 219 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-08-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 1 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-08-01T00:00:00Z"), "source" : "tweet", "offer" : "chihuahua-insurance" }, "value" : { "count" : 1 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-09-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 65 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-10-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 48 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-11-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 67 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-11-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 1 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-12-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 56 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2013-12-01T00:00:00Z"), "source" : "cpc", "offer" : "chihuahua-insurance" }, "value" : { "count" : 6 } });
> db.page_views_stats.insert({ "_id" : { "month" : ISODate("2014-01-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 7 } });

> db.page_views_stats.find({"_id.source": null,"_id.offer":"chihuahua-insurance"});

{ "_id" : { "month" : ISODate("2013-05-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 5 } }
{ "_id" : { "month" : ISODate("2013-06-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 40 } }
{ "_id" : { "month" : ISODate("2013-07-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 110 } }
{ "_id" : { "month" : ISODate("2013-08-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 219 } }
{ "_id" : { "month" : ISODate("2013-09-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 65 } }
{ "_id" : { "month" : ISODate("2013-10-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 48 } }
{ "_id" : { "month" : ISODate("2013-11-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 67 } }
{ "_id" : { "month" : ISODate("2013-12-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 56 } }
{ "_id" : { "month" : ISODate("2014-01-01T00:00:00Z"), "source" : null, "offer" : "chihuahua-insurance" }, "value" : { "count" : 7 } }

Hi @Derick thanks for giving this a go. That query definitely doesn't work for me against mongohq.com so I am wondering if it is a mongo version issues. My db at mongohq is running on 2.4.5 - what did you import to?
I have raised a ticket with mongohq as they suggested - I will post any response back here

Community · Accepted Answer · 2017-05-23 10:32:20Z

0

I have found an answer to this now in this question. The query needs to be db.page_views_stats.find({"_id.source":{ $type: 6 },"_id.offer":"chihuahua-insurance"}). I am not sure why 6 rather than 10 as stated in the mongodb docs

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Jan 7, 2014 at 10:19

guyf

811 silver badge8 bronze badges

Collectives™ on Stack Overflow

MongoDB query for null

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related