Text-to-SQL for MongoDB: natural language to aggregation pipelines

Text to MongoDB query is the same problem as text-to-SQL with a harder schema story. In a relational warehouse the columns are at least declared; in MongoDB a collection can hold documents with different shapes, nested arrays and fields that appeared last quarter. The solution is the same as for SQL, applied more strictly: a semantic model of your collections that the model selects from, a compiler that emits aggregation pipelines, a validator that refuses dangerous stages, and a golden set that measures accuracy. This article sets out how that works, what is different from SQL, and what a build involves.

Why MongoDB natural-language querying is different

Three things make MongoDB harder than SQL for a language model. First, schema is implicit: the model cannot read a CREATE TABLE, and sampling documents gives a partial picture. Second, the query language is a pipeline of stages, and the stages that join ($lookup) and flatten ($unwind) are easy to get subtly wrong, particularly with arrays. Third, MongoDB is often the operational database of an application, not a reporting store, so a careless pipeline lands on the same cluster that serves customers. Each of these has an engineering answer; none of them is "use a bigger model".

SQL versus MongoDB: what changes in the design

Build a semantic model of the collections

The first job is a document, kept in version control, that describes each collection the tool may query: the fields that matter, their types, which are arrays, which reference other collections, and what each business term means. "Order value" is the sum of items.price times items.qty after unwinding items, excluding cancelled orders. "Active customer" is one with an order in the last ninety days. The model never sees raw collection dumps; it sees this catalogue, with synonyms and examples. Sampling documents helps you write the model and helps detect drift, but the catalogue is the contract. This is the MongoDB form of the semantic layer, and it is where most of the accuracy comes from.

Generate a request, compile a pipeline

As with SQL, the model's output is a structured request: metric, dimensions, filters, time grain, sort, limit. The compiler turns that into stages from templates in the semantic model: a $match built from the filters and the user's access scope, an $unwind where the metric needs it, a $group on the dimensions, a $project for the output shape, a $sort and a $limit. Because the templates were written by an engineer who knows the array semantics, the pipeline does not double count after an unwind or lose documents on a $lookup with missing references. When a request needs a stage the templates do not provide, the tool says so rather than letting the model write raw stages.

When the model must write stages

Some teams allow the model to emit raw pipelines for exploratory questions. If you do, the validator matters more: an allow-list of stages ($match, $group, $project, $sort, $limit, $unwind, $lookup with declared collections only), a hard ban on $out, $merge, $function and $where, a check that the first $match uses an indexed field, and a maxTimeMS on every execution. The MongoDB aggregation documentation is the reference for what each stage does and costs.

Keep it off the primary

Analytics questions should not compete with checkout. Run the tool against a secondary with a read preference, or a dedicated analytics node in Atlas, with a database user that has read-only access to the analytics collections. Set maxTimeMS so a wandering pipeline is killed, and cap result sizes. If reporting load is significant, consider syncing the collections to a warehouse and running text-to-SQL there instead; the semantic model transfers almost unchanged, and the operational database is left alone. Either way, the tool should never hold credentials that can write, and its connection string should be distinct from the application's so it can be revoked independently.

Access control in a document store

There is no native row-level security policy in most MongoDB deployments, so the compiler carries the load. Every pipeline begins with a $match on the fields that scope the user: tenant id, region, owner. Those values come from the signed-in identity, never from the question. Field-level restrictions are a $project that drops sensitive fields for roles that may not see them. Negative tests attempt, per role, to reach documents outside scope, including via $lookup into a collection the role should not read, and a failure blocks release. The principles are the same as in row-level security for AI analytics.

Measuring accuracy

Collect one to three hundred real questions with verified answers and run them on every change. For MongoDB, include questions that exercise arrays ("average items per order"), nested fields ("orders by shipping city"), and lookups ("revenue by customer segment"), because those are where pipelines go wrong. Score on the result, not on the pipeline text; two correct pipelines can look different. The golden question set article explains the process in full.

A worked example

A direct-to-consumer brand ran its storefront and order management on MongoDB and its marketing team wanted to ask questions about customers, orders and campaigns without waiting for engineering. The collections were typical: orders with embedded item arrays, customers with nested addresses, and a campaigns collection referenced by id. We wrote a semantic model for the four collections that mattered, defined fifteen metrics as stage templates (order value, repeat rate, items per order, campaign-attributed revenue and so on), and built the compiler with an allow-list validator. The tool ran against an analytics node with maxTimeMS set and a read-only user. Shadow mode showed the biggest source of disagreement was cancelled and partially refunded orders, which were settled in the metric definitions. The tool then became the marketing team's front door to their data, alongside the personalisation and WhatsApp agent built on the same event stream.

Team and timeline

A text-to-MongoDB analyst is an AI engineer and a backend engineer who knows your document model, with a data owner on your side, over four to eight weeks. The first two weeks write the semantic model and the golden set; the middle weeks build the compiler, validator, identity scoping and the chat or web surface; the last weeks run shadow mode and negative tests. It is priced as natural-language data querying, from $12,500 or ₹8L, and a three-week ProofRun is the right first step to get an accuracy number on your own collections. If the tool needs to sit inside your product for customers, the SaaS copilot practice applies; see the pricing page for programs and Care Plans.

Before you start: a checklist

• List the collections users actually ask about; ignore the rest
• Sample documents per collection to find shape drift and undocumented fields
• Write the semantic model: fields, types, arrays, references, business terms
• Define metrics as stage templates, reviewed by whoever owns the number
• Create a read-only user and point the tool at a secondary or analytics node
• Set maxTimeMS, result caps and the stage allow-list
• Map identity to the $match scope fields per role and write negative tests
• Collect 100–300 real questions with verified answers

Glossary

• Aggregation pipeline: an ordered list of stages that transform documents into a result
• $match / $group / $project: the filter, aggregate and reshape stages
• $unwind: expands an array into one document per element; a common source of double counting
• $lookup: a left outer join to another collection
• maxTimeMS: a per-operation time limit that kills long-running pipelines
• Semantic model: the versioned description of collections, fields and metrics the model selects from

Questions clients ask

• Our schema changes every sprint. Will the model keep up? The semantic model is updated with the schema in the same pull request, and the golden set fails loudly when a field moves. Sampling jobs flag new fields that are not yet in the model.
• Can it handle time-series collections? Yes; bucketing by $dateTrunc is a template like any other, and time-series collections are declared with their time field and granularity.
• What about Atlas Search or vector search? Those are separate stages ($search, $vectorSearch) that can be allow-listed for text and similarity questions, with the same validation and limits.
• Does it work with Mongoose or a custom ORM? The tool queries the database directly through a read-only driver connection; the application's ORM is irrelevant to it.
• Can users see the pipeline? Yes, as formatted JSON, next to a plain-English reading of the request. Engineers use it; most users do not, but trust depends on its being there.

Related reading

See ask your database for the full set of safety controls, conversational analytics in Slack and Teams for delivery, and text-to-SQL accuracy for how to read an accuracy claim.

MongoDB does not need a different idea, only a stricter one: model the collections, compile the pipeline, validate every stage and measure the result.