current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →
up vote 0 down vote favorite

I have objects in my collection that look like:

{ MyArray:[null, "some value", null] }

I want to query those objects that have a null value as an array element. The query 

db.collection.find({"MyArray":null})

does not work, it seems to find only the objects with MyArray being null, e.g.

{ MyArray:null }

How do I need to define my query?

EDIT: Please see one of my real objects:

{
        "_id" : BinData(3,"ch9PrWveqU6niB6FGVhIOg=="),
        "PeerGroup" : "male",
        "ProductId" : BinData(3,"weRiKOtXEUSDZHkGHLcwzw=="),
        "CategoryIds" : [
                BinData(3,"BXzpwVQozECLaPkJy26t6Q=="),
                BinData(3,"ox303ZeM50KelvoUbPBJ8Q=="),
                BinData(3,"26ziaY+G9UKMyjmtVkkhcg=="),
                BinData(3,"D2X8vObte0eJHNcDfp2HBw==")
        ],
        "ShopId" : BinData(3,"ZdfPmrlKR0GkLPC4djJuKw=="),
        "BrandId" : BinData(3,"kCHyuyLvgECxPF1nxwr7qQ=="),
        "Created" : ISODate("2012-08-24T07:42:12.416Z"),
        "LastActivity" : ISODate("2013-01-14T19:38:11.776Z"),
        "Price" : 129.9,
        "Sale" : false,
        "Rating" : 11.057340703605368,
        "RatingTimed" : 0.05670431130054035,
        "Available" : null,
        "FreeDelivery" : null,
        "Attrs" : [
                null,
                null,
                null,
                null
        ]
}

And then I query by:

db.collection.find({"Attrs":null})

which yields no results.

share|improve this question
    
> db.collection.find({"MyArray":null}) gives { "_id" : ObjectId("513da72088ba1cdba2745f1e"), "MyArray" : [ null, "some value", null ] }. As expected...? – Joachim Isaksson Mar 11 '13 at 9:47
    
Query should work. Show you real data and query pls. – Kirill Zorin Mar 11 '13 at 10:13
    
Added real data and query to my question. – Max Mar 17 '13 at 14:27
    
I'm unable to reproduce your problem with your data and query - it works (MongoDB 2.4.1). What is your MongoDB version ? – ronasta Apr 2 '13 at 12:39
    
my version is 2.4.0 – Max Apr 4 '13 at 8:19
up vote 4 down vote

In order to find documents with arrays, with null elements please run:

db.collection.find({"keyWithArray":{$elemMatch:{"$in":[null], "$exists":true}}})

According to safaribooksonline proper null matching is performed using $in (because you cannot use $eq with null). Also, comparing with null: 

{"something":null}

will match documents with "something" field set to null and every document which does not have "something" field at all. Thus we have to make sure the key exists using $exists.

share|improve this answer
up vote 0 down vote

(not really an answer, but does not fit into a comment)

I can not reproduce the problem, I copied your data to the mongo shell and tried in MongoDB versions 2.2.3, 2.4.1 and here in 2.4.0:

MongoDB shell version: 2.4.0 connecting to: test

> db.version()
2.4.0
> db.xxx.insert({
... "_id" : BinData(3,"ch9PrWveqU6niB6FGVhIOg=="),
... "PeerGroup" : "male",
... "ProductId" : BinData(3,"weRiKOtXEUSDZHkGHLcwzw=="),
... "CategoryIds" : [
... BinData(3,"BXzpwVQozECLaPkJy26t6Q=="),
... BinData(3,"ox303ZeM50KelvoUbPBJ8Q=="),
... BinData(3,"26ziaY+G9UKMyjmtVkkhcg=="),
... BinData(3,"D2X8vObte0eJHNcDfp2HBw==")
... ],
... "ShopId" : BinData(3,"ZdfPmrlKR0GkLPC4djJuKw=="),
... "BrandId" : BinData(3,"kCHyuyLvgECxPF1nxwr7qQ=="),
... "Created" : ISODate("2012-08-24T07:42:12.416Z"),
... "LastActivity" : ISODate("2013-01-14T19:38:11.776Z"),
... "Price" : 129.9,
... "Sale" : false,
... "Rating" : 11.057340703605368,
... "RatingTimed" : 0.05670431130054035,
... "Available" : null,
... "FreeDelivery" : null,
... "Attrs" : [
... null,
... null,
... null,
... null
... ]
... }
... )
> db.xxx.find({"Attrs":null}).pretty()
{
    "_id" : BinData(3,"ch9PrWveqU6niB6FGVhIOg=="),
    "PeerGroup" : "male",
    "ProductId" : BinData(3,"weRiKOtXEUSDZHkGHLcwzw=="),
    "CategoryIds" : [
        BinData(3,"BXzpwVQozECLaPkJy26t6Q=="),
        BinData(3,"ox303ZeM50KelvoUbPBJ8Q=="),
        BinData(3,"26ziaY+G9UKMyjmtVkkhcg=="),
        BinData(3,"D2X8vObte0eJHNcDfp2HBw==")
    ],
    "ShopId" : BinData(3,"ZdfPmrlKR0GkLPC4djJuKw=="),
    "BrandId" : BinData(3,"kCHyuyLvgECxPF1nxwr7qQ=="),
    "Created" : ISODate("2012-08-24T07:42:12.416Z"),
    "LastActivity" : ISODate("2013-01-14T19:38:11.776Z"),
    "Price" : 129.9,
    "Sale" : false,
    "Rating" : 11.057340703605368,
    "RatingTimed" : 0.05670431130054035,
    "Available" : null,
    "FreeDelivery" : null,
    "Attrs" : [
        null,
        null,
        null,
        null
    ]
}

Please, copy the above sequence to your mongo shell and post the results here.

share|improve this answer
up vote 0 down vote accepted

Ok, I figured it out by loading my "null" candidates via my C# driver and it turns out that the values are not null, but Bson Undefined!! They are displayed as "null" in the shell! See http://jira.mongodb.org/browse/SERVER-2426. This works:

db.xxx.find({"Attrs":{$type:6}})

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.