Partial Index Equivalent to sparse

What is the method to create partial index on not null data?

It is not allowing to create partial index with not expression.
For example, following command get error:

db.getCollection('myCollection').createIndex(
   { fieldname: 1},
   { partialFilterExpression: {fieldname: {$ne:null}}}
)

I have tried different method to create partial index using below given partialFilterExpression.

{'partialFilterExpression':{'field':{'\$type':['int','double','string']}}

But, queries with { ’ field ’ : { ’ $ne ’ : null }}, { ‘field’ : value } does not use this partial index. How can I create Partial index for such queries?

Hello @Monika_Shah,

The partial index accepts the following expressions in MongoDB v6, as you can read in the documentation,

Instead, you can create an index with $exists: true expression, and make sure you don’t save property with a null value in a collection otherwise it will consider it as exist field.

And in your query you can check { 'field': { '$exists': true, '$eq': "value" } }

You can use the $or operator to check multiple types, it is supported by MongoDB v6.

1 Like

It shows errors when applied using $in with $type for both of following statement. and accept statement when works without $in.

> db.test.createIndex({"field":1},{'partialFilterExpression':{'field':{'$type':{'$in':["int","double"]}}}})

“Error in specification { key: { field: 1.0 }, name: "field_1", partialFilterExpression: { field: { $type: { $in: [ "int", "double" ] } } } } :: caused by :: type must be represented as a number or a string”,

> db.test.createIndex({"field":1},{'partialFilterExpression':{'field':{'$type':{'$in':[1,2,16,18]}}}})

{
“ok” : 0,
“errmsg” : “Error in specification { key: { field: 1.0 }, name: "field_1", partialFilterExpression: { field: { $type: { $in: [ 1.0, 2.0, 16.0, 18.0 ] } } } } :: caused by :: type must be represented as a number or a string”,
“code” : 14,
“codeName” : “TypeMismatch”

numbers or string

As you can read from the error message, The $type operator does not allow another operator as value except its types, instead, you can use $or operator,

db.test.createIndex(
  { "field": 1 }, 
  { 
    'partialFilterExpression' : { 
      "$or": [{ "field": { "$type": "int" } }, { "field": { "$type": "double" } }] 
    } 
  }
)