index on nested array

MongoDB Atlas
1

Hi Pavel,

i have document where “secKeys” were exists 25%. i want to create partial index on this array

which one is best approach we still 5.0 and yet to migrate 6

  1. partial index on nested array element
  2. multikey index on nester array element
  3. wilcard match on nester array element

_id
tenantID
OrderCreatedDateTime
“secondaryKeys” : [
{
“storeName” : “RxHome”,
“keyName” : “OrderId”,
“keyComponents” : [
{
“name” : “SvcbrId”,
“value” : “888”
},
{
“name” : “PrescriptionId”,
“value” : “3333124”
},
{
“name” : “RefillNo”,
“value” : “2”
}
]
}
],

My Index:

Partial Index on Array: (give me correct syntax it doesn’t allow me create in studio 3T)

db.Order.createIndex(
{ “orderCreateDateTime”: 1 },{ “tenantId”: 1 },
{ unique: true, partialFilterExpression: { “secondaryKeys.storeName”: { $exists: true } } }
)

WildCardIndex Index on Array : supports only 7.0 pls correct me

db.Order.createIndex(
{
“orderCreatedDateTime”: 1,
“tenantId”: 1,
“secondaryKeys.$**”: 1
}
)

Multikey Index on Nested Array element:

db.Order.createIndex(
{ “orderCreateDateTime”: 1 },{ “tenantId”: 1 },
{ sparse: true, “secondaryKeys.storeName”: 1 } }
)

which one suits best on 5 , 6 and 7 versions and pls share working command in studio 3T or mongo shell.