I have a dynamic schema on MongoDB for different workflow templates. Each workflow template has data attributes associated which is stored as normal <key,value>
[{
_id : "1",
team_id : "ABC",
template_id : "retail_sales",
document_id : "d1",
data : [
{ k : "team_id", v : "ABC" },
{ k : "template_id", v : "retail_sales" },
{ k : "recorded_at", v : "2021-10-01T00:00" },
{ k : "billing_state", v : "KA" },
{ k : "pin_code", v : 50001 },
]
},
{
_id : 2
team_id : "ABC",
template_id : "simple_crm",
document_id : "d2",
data : [
{ k : "team_id", v : "ABC" },
{ k : "template_id", v : "simple_crm" },
{ k : "recorded_at", v : "2021-10-01T00:00" },
{ k : "status", v : "KA" },
{ k : "loan_amount", v : 10000 },
]
}]
I have a compound index as follows
{
team_id : 1,
template_id : 1,
document_id : 1,
"meta.k" : 1,
"meta.v" : 1
}
I am looking at optimising my MongoDB aggregation queries and wanted to check how I could have a query with the covered index.
For example, I am looking at the query like below
db.getCollection("<>").aggregate(
[
{
$match : {
"team_id" : "ABC",
"template_id" : "retail_sales",
data: {
$all: [
{
$elemMatch: {
k: "billing_state",
v: "KA",
},
}
]
}
}
},
{
$project: {
_id: 0,
"document_id": 1
},
},
],
{
hint : "idx_1"
}
)
However, the query plan shows that it’s still fetching data and not using the covered index.
Is it possible to query use the covered index where the index is on array of objects?
Additional Details
On looking in to the documentation at https://docs.mongodb.com/manual/core/query-optimization/#std-label-multikey-covering it appears that queries cannot be covered using the multikey indexes.
If multikey indexes cannot be used to cover the query what is the ideal solution to store and query large volume of dynamic data?