MongoDb query array with null values

Question

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.

> db.collection.find({"MyArray":null}) gives { "_id" : ObjectId("513da72088ba1cdba2745f1e"), "MyArray" : [ null, "some value", null ] }. As expected...? — Joachim Isaksson
– Joachim Isaksson, Commented Mar 11, 2013 at 9:47
I'm unable to reproduce your problem with your data and query - it works (MongoDB 2.4.1). What is your MongoDB version ? — ronasta
– ronasta, Commented Apr 2, 2013 at 12:39
It would be great if you could mark my answer. Don't want to be rude about it, but quite often I come back to it as a reference on my own :) — Krystian
– Krystian, Commented Nov 17, 2016 at 7:28

Krystian · Accepted Answer · 2015-04-14 11:34:36Z

42

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.

edited Apr 14, 2015 at 11:34

answered Apr 14, 2015 at 11:27

Krystian

3,4334 gold badges41 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Praveen Kumar Verma · Accepted Answer · 2023-08-21 13:30:39Z

1

MongoDb query with null value filter:

{field: {$in: [null]}}

answered Aug 21, 2023 at 13:30

Praveen Kumar Verma

3,5602 gold badges23 silver badges34 bronze badges

Comments

ronasta · Accepted Answer · 2013-04-07 03:59:54Z

(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.

Mads · Accepted Answer · 2013-04-08 07:50:18Z

-1

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

answered Apr 8, 2013 at 7:50

Mads

1,2162 gold badges11 silver badges30 bronze badges

Collectives™ on Stack Overflow

MongoDb query array with null values

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related