Partial Indexes

[Laks]

Hello Team,

Requirement is to identify transactions with the
{"Type": "Retail", "ArchivalStatus": {"$exists": false}, "CreatedDate": ISO Date } and update a new field → {"ArchivalStatus": "COMPLETED"} .
Below is the sample structure.

{"name" : "ABC", "details" : {...}, "ArchivalStatus": "COMPLETE", "CreatedDate": ISO Date() }

I’m planning to create the below partial index to pick only those records not having ArchivalStatus field.

db.coll.createIndex(
   { "Type": 1},
   { partialFilterExpression: { "ArchivalStatus": { $eq: null } } }
);

Below are the queries with respect to partial indexes in mongo version 4.4.

1. why is the below index with $exists : false not supported ?

db.RV.createIndex(
   { "Type": 1},
   { partialFilterExpression: { "match_res.enrichmentstatus": { $exists: false } } }
);

2. Please help me understand the pre requisite to create partial indexes with ‘$and’ operator at the top-level only (https://www.mongodb.com/docs/v5.0/core/index-partial/) with an example.

3. How to create partial index with multiple fields in mongo version 4.4

Regards,
Laks

[Laks]

Hi Team,

Do you any updates on the above query?

Regards,
Laks

[steevej (Steeve Juneau)]

@Laks

why is the below index with $exists : false not supported ?

Because the documentation says that $exists:true is allowed. If $exists:false would be allow the documentation will have mentioned. There might be a technical reason why it cannot be supported. One thing about partial index is that the query must specify the fields used in the expression. This makes me think how would you specify a query that specify a field that does not exists.

You probably could reverse your logic and have start with enrichmentstatus:null rather than non existing status and have your partial index expression to be enrichmentstatus:null.

@Laks

the requisite to create partial indexes with ‘$and’ operator at the top-level

example:

c.createIndex( { last_updated : 1 } , { partialFilterExpression: { "$and" : [ { name : "Tips" } , { type : "generic"} ] }})

By top-level only, I could only assume that $and has to come first. But since the operators $not and $or are not allowed, you could probably re-write any expression with $and somewhere else as an $and at the top level.