Index MongoDB Collection by inner keys

Question

I have documents in a collection which look like this:

`{
"_id": { "$oid": "63c6c823131d0b01d353b8d7" },
"customer_id": "5a4c8b63b7055a9109477c5b",
"couponId": "63c6c823131d0b01d353b8d6",
"prefix": 999,
"amount": 1000,
"used": 12,
"keys": {
     "375354522485": {
         "id": "375354522485",
         "used": true
     },
     "375354538550": {
         "id": "375354538550",
         "used": false
     },
     "375354549291": {
         "id": "375354549291",
         "used": false
     }
   }
}`

the amount of keys in the object can be thousands (200,000)

I am trying to index the keys by id in mongoose like this:

CouponSeriesSchema.index({ 'keys.*$*.id': 1 });

but the index size does not make sense, it is 20.5KB while the _id index is 36.9KB I would expect this index size to be much bigger How should I index the id's?

Answer 1

A smarter design would be this:

{
"_id": { "$oid": "63c6c823131d0b01d353b8d7" },
"customer_id": "5a4c8b63b7055a9109477c5b",
"couponId": "63c6c823131d0b01d353b8d6",
"prefix": 999,
"amount": 1000,
"used": 12,
"keys": [
     {
         "id": "375354522485",
         "used": true
     },
     {
         "id": "375354538550",
         "used": false
     },
     {
         "id": "375354549291",
         "used": false
     }
   ]
}

Then an index { 'keys.id': 1 } would work.