Hello World of MongoDB playing with Index on Embedded Document Fields within Arrays

mongo-index-on-fields-in-array.md

MongoDB Hello World index on embedded document fields within arrays

Reference: https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/create-multikey-index-embedded/

Start MongoDB

# in one terminal
docker run -it --rm -p 27017:27017 mongo:6

# first install mongosh
# then in another terminal
mongosh

In Mongosh

db.createCollection("col");
db.col.insertOne({
  "idx": [
    {"k": "name", "v": "ryan"},
    {"k": "height", "v": 999},
    {"k": "state", "v": "CA"}
  ],
  "person": true,
  "car": "Toyota",
  "food": "Pizza"
});

db.col.insertOne({
  "idx": [
    {"k": "brand", "v": "anker"},
    {"k": "code", "v": 383812},
    {"k": "type", "v": "charger"}
  ],
  "price": 44,
  "size": 11,
  "location": "US"
});

db.col.insertOne({
  "idx": [
    {"k": "owner", "v": "apple"},
    {"k": "device", "v": "mba2022"},
    {"k": "price", "v": 2000}
  ],
  "quantity": 43334,
  "weight": 122,
  "location": "JPN"
});

db.col.createIndex({
  "idx.k": 1,
  "idx.v": 1
});

db.col.find({idx: {$elemMatch: {k : "brand", v: "anker" }}});

db.col.insertOne({
  "idx": [
    {"k": "brand", "v": "anker"},
    {"k": "code", "v": 33333},
    {"k": "type", "v": "stand"}
  ],
  "price": 33,
  "size": 44,
  "location": "US"
});

db.col.insertOne({
  "idx": [
    {"k": "brand", "v": "sonos"},
    {"k": "code", "v": 444},
    {"k": "type", "v": "speaker1"}
  ],
  "price": 444,
  "size": 112,
  "location": "US"
});

db.col.find({idx: {$elemMatch: {k : "brand", v: {$in: ["anker", "sonos"]} }}});

db.col.insertOne({
  "idx": [
    {"k": "brand", "v": "sonos"},
    {"k": "code", "v": 4511},
    {"k": "type", "v": "stand"}
  ],
  "price": 41,
  "size": 12,
  "location": "US"
});

db.col.find(
  {$and: [
    {idx: {$elemMatch: {k : "brand", v: {$in: ["anker", "sonos"]} }}},
    {idx: {$elemMatch: {k : "type", v: "stand" }}}
  ]}
);

db.col.find(
  {$and: [
    {idx: {$elemMatch: {k : "brand", v: "anker" }}},
    {idx: {$elemMatch: {k : "code", v: {$gt: 33333} }}}
  ]}
);

All of these find() queries are index scans 👍 At this point you can play with MongoDB Compass and use the query explainer for more visual views

But this suffers the same gotcha as wildcard indices with the single predicate on the search 😞 https://www.mongodb.com/docs/manual/core/indexes/index-types/index-wildcard/reference/restrictions/#multi-field-query-predicates