CODEWITHSUNDEEP
mongodb
guyf
guyf

Reputation: 81

MongoDB query for null

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?

Upvotes: 0

Views: 316

Answers (2)

guyf
guyf

Reputation: 81

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

Upvotes: 0

Derick
Derick

Reputation: 36784

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 } }

Upvotes: 2

Related Questions