Simplifying MongoDB Aggregations with Plain English

MongoDB's aggregation framework is incredibly powerful, allowing you to process and transform documents in various ways. From simple filtering and projection to complex joins and data reshaping, aggregations are a cornerstone of advanced MongoDB queries. However, writing these pipelines can quickly become verbose and complex, especially for operations involving multiple stages like $lookup for joins or $group for analytics.

Let's consider a common scenario: you have users and orders collections. Each order belongs to a user. You want to find all active users who have placed at least one order, and for each user, list their name, email, and the total number of orders they've made, sorted by the number of orders in descending order.

The Challenge with Raw MongoDB Aggregations

Translating this intent into a MongoDB aggregation pipeline involves several stages. You'd typically start by filtering active users, then perform a $lookup to join with orders, unwind the orders array, filter out users without orders, group by user to count orders, and finally project the desired fields and sort. Here's what that might look like:

const pipeline = [
  { $match: { status: 'active' } },
  {
    $lookup: {
      from: 'orders',
      localField: '_id',
      foreignField: 'userId',
      as: 'userOrders',
    },
  },
  { $unwind: '$userOrders' }, // We need to unwind to filter out users without orders effectively
  { $group: {
      _id: '$_id',
      name: { $first: '$name' },
      email: { $first: '$email' },
      orderCount: { $sum: 1 },
    },
  },
  { $match: { orderCount: { $gt: 0 } } }, // Filter out users who had no orders after unwind
  { $project: { _id: 0, name: 1, email: 1, orderCount: 1 } },
  { $sort: { orderCount: -1 } },
];

// Assuming 'db' is your connected MongoDB database instance
// const result = await db.collection('users').aggregate(pipeline).toArray();
// console.log(result);

This pipeline, while functional, is quite a mouthful. It requires precise knowledge of aggregation operators, field paths, and the order of operations. Debugging can be tricky, and for new team members, understanding the intent from the code alone can take time.

Defining Your Models

To simplify this, the first step is to clearly define your data models. This provides the compiler with the necessary context about your collections and their relationships. For our example, we'd define Users and Orders:

const { MaskModels } = require('mask-databases');

MaskModels.define(
  'Users. Collection users. People who sign into the app. Their full name, the ' +
  'email they log in with (two people must not share the same email), and whether ' +
  'the account is active or turned off.'
);

MaskModels.define(
  'Orders. Collection orders. Each order belongs to one customer. The order amount ' +
  'and the date it was placed.'
);

These definitions tell the system about your users and orders collections, the fields they contain, and the relationship between them (each order belongs to one customer).

Expressing Intent in Plain English

Once your models are defined and compiled (by running node mask.compile.cjs), you can express your query intent in natural language. The system will then generate the appropriate MongoDB aggregation pipeline for you.

To achieve our original goal – finding active users with orders, their name, email, and total order count, sorted by count – you could write a prompt like this:

const { MaskDatabase } = require('mask-databases');

async function getActiveUsersWithOrderCounts() {
  const usersWithOrders = await MaskDatabase.prompt(
    'list active users, their name, email, and how many orders they made, sorted by order count descending'
  );
  console.log(usersWithOrders);
}

// getActiveUsersWithOrderCounts();

The compiler translates this plain English into a robust MongoDB aggregation pipeline that handles the $match, $lookup, $unwind, $group, $project, and $sort stages automatically. This approach significantly enhances readability, making your data access logic self-documenting and easier to maintain.

The Benefits of Natural Language Queries

This method of database interaction offers several advantages: it's highly readable, resembling documentation; it's deterministic because the AI runs only at compile time, not at runtime; and it's schema-aware, ensuring the generated queries fit your exact data model. This approach is also engine-portable, meaning the same English prompt can generate queries for different databases like MySQL or PostgreSQL if your configuration changes.

Tools like Mask Databases provide this natural-language ORM for Node.js and TypeScript, allowing you to describe models and queries in plain English, which are then compiled into real database code. You can explore how it works in their live playground: https://maskdatabases.com/playground.