0

Data: 

"field1" : { "sub_field" : [ ]}

I want to write a query to check if the 'sub_field' is empty or not.

This is what I tried:

$cursor = $collection->find( array('field1.sub_field' => array('$ne' => null))

Obviously it gives results as Array is not null (I have tried null and empty space in futility).

I was told that '$size' operator can be used to achieve this. but I have had no luck so far.

Any suggestions ?

Mobs
  • 28
  • 7

2 Answers2

2

for find type null or undefined field you can use this :

For undefined : 

db.getCollection('your_collection_name').find({ yuorField: { $type: 6 } })

For null :

db.getCollection('your_collection_name').find({ yuorField: { $type: 10 } })
Muhammad Hassaan
  • 7,296
  • 6
  • 30
  • 50
Samir Sayyad
  • 109
  • 1
  • 6
  • Its not a case of null value. its a case of empty array. How would we check if the array is empty or not in a query ? – Mobs Jan 06 '16 at 09:01
  • The solution works for NULL value excellently. However I guess an empty array is not equivalent to 'undefined'. The return is always empty for $Type 6 checks. – Mobs Jan 07 '16 at 04:03
  • it just check undefined and null fields. – Samir Sayyad Feb 15 '16 at 11:32
1

You could approach this in a couple of ways. The first is to use the numeric array indexes in the query object keys using the dot notation and the $exists operator to search all documents that won't have at least a sub_field array element:

var cursor = db.collection.find({ "field1.sub_field.0": { "$exists": false } })

which should translate to PHP as

$cursor = $collection->find( array("field1.sub_field.0" => array("$exists" => false))

The other way would be to use the $size operator together with the $exists operator all wrapped within a $or operator to find all documents without a sub_field either non existent or empty array:

var cursor = db.collection.find({
    "$or": [
        { "field1.sub_field": { "$exists": false } },
        { "field1.sub_field": { "$size": 0 } }
    ]
});

Another approach you could consider though with slower performance, would be to use the $where operator:

var cursor = db.collection.find({       
    "$where": "this.field1.sub_field.length == 0"   
});

For benchmark testing, try populating a test collection:

db.test.insert([       
    { field1: { sub_field: [] } },
    { field1: { sub_field: [ "foo" ] } },
    { field1: { sub_field: [ "foo", "bar" ] } }
]);

> db.test.find({ "field1.sub_field.0": { "$exists": false } })
> db.test.find({
    "$or": [
        { "field1.sub_field": { "$exists": false } },
        { "field1.sub_field": { "$size": 0 } }
    ]
})
> db.test.find({ "$where": "this.field1.sub_field.length == 0" })

All the three queries will yield the document that has an empty sub_field array:

/* 0 */
{
    "_id" : ObjectId("568ccec3653d87e43482c4d0"),
    "field1" : {
        "sub_field" : []
    }
}
chridam
  • 100,957
  • 23
  • 236
  • 235
  • I haven't tried the solution yet. but one note. The sub_field will never be null. it could be an empty array array(0). But never null. that's where I have the issue. I will try the solutions and post the results here. – Mobs Jan 06 '16 at 08:59
  • The queries would still work nonetheless. I have removed the obsolete test cases and can confirm the result is still the same. – chridam Jan 06 '16 at 09:09
  • Thanks. even though I haven't resolved the issue, I am pretty much convinced that this is the way to go. when I tried '$where' condition with the 'length' option I received an error : "TypeError: Cannot read property 'length' of undefined near 'field.sub_field.length". I am using Mongodb 2.6.11. is there any compatibility issues ? – Mobs Jan 07 '16 at 03:44
  • 1
    No issues with 2.6.11 as far as I know. The error is most probably as a result of some documents which don't have the `sub_field` key, hence the `undefined` error. – chridam Jan 07 '16 at 07:07
  • 1
    This worked: $cursor = $collection->find(array("field.sub_field.0" => array('$exists' => true))); – Mobs Jan 07 '16 at 07:18