Views, on-demand materialized views and collation

conceptThe concept

A view is a named aggregation pipeline over a source collection. It stores no data: every read runs the pipeline. Views are read-only, cannot be indexed themselves, and benefit from source indexes when the pipeline starts with $match. Typical uses: hiding PII from analysts, exposing a stable shape to a reporting tool.

An on-demand materialized view is a regular collection refreshed by a pipeline that ends in $merge (4.2+). It can be indexed and read cheaply, at the cost of staleness between refreshes.

Collation controls string comparison: { locale: "en", strength: 2 } ignores case, strength: 1 also ignores accents, the default strength: 3 compares both. It applies to matching, sorting and index keys. An index is only used for string comparisons when the query's collation matches the index's collation.

whyWhy it matters

Case-insensitive login lookups, alphabetical lists in local languages, and "give analysts a safe subset" are everyday requests. The usual wrong answers are unanchored case-insensitive regexes (which scan) or duplicating data by hand.

Use caseReal-world scenario

An identity service stores emails as entered. Logins must match regardless of case. The team creates { email: 1 } with collation: { locale: "en", strength: 2 } and unique, so "Asha@Example.com" and "asha@example.com" cannot both exist, and the login query specifies the same collation so it uses the index.

Try itExamples

Run and edit these in the interactive lesson.

db.v_customers_public.drop()
db.createView("v_customers_public", "customers", [
  { $match: { optIn: true } },
  { $project: { _id: 1, tier: 1, city: "$address.city", country: "$address.country" } }
])
db.v_customers_public.find().limit(3)

db.v_customers_public.insertOne({ tier: "gold" })

db.orders.aggregate([
  { $match: { status: { $in: ["paid", "shipped", "delivered"] } } },
  { $group: { _id: "$shipping.country", orders: { $sum: 1 }, revenue: { $sum: "$total" } } },
  { $set: { revenue: { $round: ["$revenue", 2] }, refreshedAt: "$$NOW" } },
  { $merge: { into: "mv_revenue_by_country", whenMatched: "replace", whenNotMatched: "insert" } }
])
db.mv_revenue_by_country.find().sort({ revenue: -1 }).limit(3)

db.lab_people.drop()
db.lab_people.insertMany([{ email: "Asha@Example.com" }, { email: "asha@example.com" }, { email: "ASHA@EXAMPLE.COM" }, { email: "ravi@example.com" }])
db.lab_people.find({ email: "asha@example.com" }).count()
db.lab_people.find({ email: "asha@example.com" }).collation({ locale: "en", strength: 2 }).count()

db.lab_names.drop()
db.lab_names.insertMany([{ n: "banana" }, { n: "Apple" }, { n: "apple" }, { n: "Zebra" }]);
({
  binary: db.lab_names.find().sort({ n: 1 }).toArray().map(d => d.n),
  english: db.lab_names.find().sort({ n: 1 }).collation({ locale: "en" }).toArray().map(d => d.n)
})

db.lab_logins.drop()
db.lab_logins.createIndex({ email: 1 }, { unique: true, collation: { locale: "en", strength: 2 } })
db.lab_logins.insertOne({ email: "Meera@Example.com" })
db.lab_logins.getIndexes()[1]

internalsUnder the hood

Collation-aware indexes store ICU sort keys instead of raw strings, so a case-insensitive equality is a normal index seek. A collection can have a default collation set at creation; queries without an explicit collation inherit it. Views inherit the source collection's default collation and cannot use a different one.

Avoid / preferDo this, not that

db.users.createIndex({ email: 1 }, { unique: true })
// "Asha@x.com" and "asha@x.com" both accepted

db.users.createIndex({ email: 1 }, { unique: true, collation: { locale: "en", strength: 2 } })

// give the reporting team read access to customers, then trust them to ignore email

db.createView("customer_report", "customers", [{ $project: { name: 1, tier: 1, country: "$address.country" } }])
// grant read on customer_report only

PitfallsCommon mistakes

• Using /^asha@example.com$/i for logins, which cannot use a normal index efficiently.
• Creating a collation index but querying without the same collation, so the index is not used for the string comparison.
• Expecting a view to be faster than the underlying query; it is the same query.
• Forgetting to refresh materialized views, or refreshing them with $out (which replaces the collection) when incremental $merge was intended.

LimitsWhen not to use it

Standard views run their pipeline on every query, so a heavy view over a large collection is slow every time. Precompute with $merge into a real collection (an on-demand materialised view) when reads are frequent. Collation is not free either: indexes and queries must agree on it, or the index is ignored.

SecuritySecurity notes

Views are a practical least-privilege tool: grant find on the view, not on the source collection. Users with read on the source can bypass the view, so check role grants, not just the existence of the view.

ChallengeTry it yourself

db.platinum_contacts_lab.drop()
db.createView("platinum_contacts_lab", "customers", [
  { $match: { tier: "platinum" } },
  { $project: { _id: 0, name: 1, country: "$address.country" } }
])
db.platinum_contacts_lab.find().limit(3)

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.