MongoDB find: Filters, Projections and Sorting for SQL People

MongoDB find returns the documents that match a filter. It does the work of SELECT, WHERE, ORDER BY and TOP in one call. If you come from SQL Server, the curly braces take a few days to read comfortably. This post puts each find next to its SQL equivalent.

A Grocery Collection to Query

I ran every example below on MongoDB 9.0.2 with mongosh 2.13.0. The collection holds 18 vegetarian grocery products. Each product has a name, a category, a price, a stock count and an organic flag. If the vocabulary is new, my post on SQL terms vs MongoDB terms maps the terms.

use findDemo
db.products.insertMany([
  { _id: 1,  name: 'Basmati rice',      category: 'grains',     price: 4.99, stock: 40,  organic: false },
  { _id: 2,  name: 'Brown rice',        category: 'grains',     price: 3.79, stock: 55,  organic: true },
  { _id: 3,  name: 'Rolled oats',       category: 'grains',     price: 3.29, stock: 80,  organic: true },
  { _id: 4,  name: 'Whole wheat flour', category: 'grains',     price: 2.99, stock: 0,   organic: false },
  { _id: 5,  name: 'Red lentils',       category: 'legumes',    price: 2.49, stock: 120, organic: true },
  { _id: 6,  name: 'Chickpeas',         category: 'legumes',    price: 1.99, stock: 95,  organic: false },
  { _id: 7,  name: 'Black beans',       category: 'legumes',    price: 1.79, stock: 60,  organic: false },
  { _id: 8,  name: 'Spinach',           category: 'vegetables', price: 2.29, stock: 25,  organic: true },
  { _id: 9,  name: 'Carrots',           category: 'vegetables', price: 1.49, stock: 70,  organic: true },
  { _id: 10, name: 'Broccoli',          category: 'vegetables', price: 2.19, stock: 0,   organic: false },
  { _id: 11, name: 'Tomatoes',          category: 'vegetables', price: 3.09, stock: 35,  organic: false },
  { _id: 12, name: 'Bananas',           category: 'fruit',      price: 0.69, stock: 150, organic: false },
  { _id: 13, name: 'Apples',            category: 'fruit',      price: 1.99, stock: 90,  organic: true },
  { _id: 14, name: 'Mangoes',           category: 'fruit',      price: 1.59, stock: 20,  organic: false },
  { _id: 15, name: 'Greek yogurt',      category: 'dairy',      price: 4.49, stock: 30,  organic: true },
  { _id: 16, name: 'Paneer',            category: 'dairy',      price: 5.99, stock: 15,  organic: false },
  { _id: 17, name: 'Oat milk',          category: 'drinks',     price: 3.99, stock: 45,  organic: true },
  { _id: 18, name: 'Almonds',           category: 'snacks',     price: 7.49, stock: 50,  organic: false }
])

find and findOne

The find method takes two arguments: a filter and a projection. The filter says which documents to return, and the projection says which fields to show. With an empty filter, find matches every document. It does not return an array. It returns a cursor, which is a pointer that hands out documents in batches. The shell prints the first 20 for you.

The findOne method returns the first matching document itself. When nothing matches, it returns null. Both calls below use plain equality. The countDocuments method counts the matches, and an empty filter counts everything.

db.products.countDocuments({})
// 18
db.products.findOne({ name: 'Paneer' })
// { _id: 16, name: 'Paneer', category: 'dairy', price: 5.99, stock: 15, organic: false }

Filters With Operators

A filter is a document. A field with a plain value means equality. A field with an operator document means a comparison, and every operator starts with a dollar sign. The operator $gt means greater than, and $gte, $lt and $lte follow the same pattern. Here are all products that cost more than 4 dollars.

db.products.find({ price: { $gt: 4 } }, { _id: 0, name: 1, price: 1 })
// { name: 'Basmati rice', price: 4.99 }
// { name: 'Greek yogurt', price: 4.49 }
// { name: 'Paneer', price: 5.99 }
// { name: 'Almonds', price: 7.49 }

Two operators on the same field make a range. The next filter finds products from 2 to 3 dollars. After it, $in matches any value from a list, like IN in SQL.

db.products.find({ price: { $gte: 2, $lte: 3 } })
// Whole wheat flour, Red lentils, Spinach, Broccoli
db.products.find({ category: { $in: ['fruit', 'vegetables'] } })
// Spinach, Carrots, Broccoli, Tomatoes, Bananas, Apples, Mangoes

The comments above show only the name field of each result, to save space. To combine conditions on different fields, list them side by side. MongoDB joins them with AND. The $and operator says the same thing in a longer form. In my test, both versions returned Brown rice and Rolled oats.

db.products.find({ category: 'grains', organic: true })
db.products.find({ $and: [{ category: 'grains' }, { organic: true }] })
// Brown rice, Rolled oats

The long form has a real use. A JavaScript object can’t hold the same key twice, so a second condition on one field would overwrite the first. Wrap the conditions in $and when that happens. For OR, there is no shortcut: use $or with a list of filters.

db.products.find({ $or: [{ stock: 0 }, { price: { $lt: 1.5 } }] }, { _id: 0, name: 1, price: 1, stock: 1 })
// { name: 'Whole wheat flour', price: 2.99, stock: 0 }
// { name: 'Carrots', price: 1.49, stock: 70 }
// { name: 'Broccoli', price: 2.19, stock: 0 }
// { name: 'Bananas', price: 0.69, stock: 150 }

Projection: The SELECT List

The second argument of find is the projection. A 1 includes a field, and a 0 excludes it. The _id field shows up unless you turn it off, which is why the examples above used _id: 0. This one returns only the name and price of the first two products.

db.products.find({ _id: { $lte: 2 } }, { _id: 0, name: 1, price: 1 })
// { name: 'Basmati rice', price: 4.99 }
// { name: 'Brown rice', price: 3.79 }

An exclusion list works the other way. It returns everything except the fields you name. You can’t mix the two styles in one projection, with the single exception of _id. MongoDB refuses the mix with an error.

db.products.find({ _id: 1 }, { category: 0, organic: 0 })
// { _id: 1, name: 'Basmati rice', price: 4.99, stock: 40 }
try { db.products.find({}, { name: 1, price: 0 }).toArray(); } catch (e) { print(e.message); }
// Cannot do exclusion on field price in inclusion projection

Sort, Limit and Skip

The sort method takes a document too. A 1 sorts ascending, and a -1 sorts descending. The limit method caps the number of documents. Together they are the MongoDB version of SELECT TOP 3 with ORDER BY. Here are the three most expensive products.

db.products.find({}, { _id: 0, name: 1, price: 1 }).sort({ price: -1 }).limit(3)
// { name: 'Almonds', price: 7.49 }
// { name: 'Paneer', price: 5.99 }
// { name: 'Basmati rice', price: 4.99 }

The skip method jumps over documents, which gives you paging. It is the cousin of OFFSET in SQL Server. MongoDB applies the sort first, then the skip and then the limit, whatever order you write them in. Here is the second page of three, after the three above.

db.products.find({}, { _id: 0, name: 1, price: 1 }).sort({ price: -1 }).skip(3).limit(3)
// { name: 'Greek yogurt', price: 4.49 }
// { name: 'Oat milk', price: 3.99 }
// { name: 'Brown rice', price: 3.79 }

A sort can have several keys, in the order you list them. This one sorts by category first, then by price from high to low. It also counts documents with a filter, as SELECT COUNT(*) does.

db.products.find({ category: { $in: ['grains', 'legumes'] } }, { _id: 0, name: 1, category: 1, price: 1 }).sort({ category: 1, price: -1 })
// { name: 'Basmati rice', category: 'grains', price: 4.99 }
// { name: 'Brown rice', category: 'grains', price: 3.79 }
// { name: 'Rolled oats', category: 'grains', price: 3.29 }
// { name: 'Whole wheat flour', category: 'grains', price: 2.99 }
// { name: 'Red lentils', category: 'legumes', price: 2.49 }
// { name: 'Chickpeas', category: 'legumes', price: 1.99 }
// { name: 'Black beans', category: 'legumes', price: 1.79 }
db.products.countDocuments({ stock: 0 })
// 2
db.products.countDocuments({ organic: true, price: { $lt: 3 } })
// 4

Two Quiet Surprises

SQL Server rejects a column that doesn’t exist. MongoDB find does not, because it doesn’t know your fields in advance. A filter on a missing field is valid and matches nothing. A filter that compares the wrong type also matches nothing, and that includes the string ‘0’ against the number 0.

db.products.find({ color: 'green' })
// (no documents)
db.products.countDocuments({ stock: '0' })
// 0
db.products.countDocuments({ stock: 0 })
// 2

Neither query raised an error, and the zero looks like an honest answer. A scheduled job that counts documents will happily report it. When a result is empty and you expected data, check the field spelling first. Then check the type of the value.

The cursor deserves one more note. A cursor hands out documents as you ask for them, instead of loading them all at once. In my test, hasNext returned true. Two calls to next returned Basmati rice and Brown rice. The toArray call then returned the remaining 16 documents as an array.

You could say SQL reads better. The statement SELECT Name FROM Products WHERE Price > 4 is almost English. Fair point. For a single query, SQL wins on readability. But a MongoDB filter is plain data. Your code can build it, store it and pass it around without gluing strings together.

A Short Checklist

Count with a filter before you act on it, and read a few documents. Use the same filter for the later update or delete. Project only the fields you need, because smaller results travel faster. Always sort when you limit to the top rows. A limit alone returns documents in no promised order. Write field names carefully, and keep one type per field.

MongoDB find gets faster with the right index, so add one on the fields you filter and sort by. On a large collection, that matters. A sort that follows an index can skip the sorting work. When you finish testing, remove the example database.

use findDemo
db.dropDatabase()

MongoDB find is not SELECT with different punctuation, it is a filter, a field list and a cursor working together.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.