DQL now supports projections and aggregates in addition to SELECT * operations. You can select specific fields, perform calculations, and use aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

SELECT Statements

The following syntax outlines the basic structure and optional clauses you can use within your SELECT statements:
DQL
SELECT [DISTINCT] projection
FROM your_collection_name
[WHERE condition]
[GROUP BY groupby_expression_1, groupby_expression_2, ...]
[HAVING condition]
[ORDER BY orderby_expression_1, orderby_expression_2, ... [ASC|DESC]]
[LIMIT limit_value]
[OFFSET number_of_documents_to_skip]
Where projection can be:
  • * - Returns all fields from the documents
  • field1, field2, ... - Returns specific fields
  • expression AS alias - Returns calculated values with custom names
  • Aggregate functions like COUNT(*), SUM(field), etc.
For instance, retrieve all documents in the cars collection WHERE the color property is set to the value ‘blue’ :
DQL
SELECT * FROM cars WHERE color = 'blue'
To perform a SELECT and receive all document fields, including an embedded MAP:

Clauses for Filtering

The following table provides an overview of the different clauses you can use to define specific conditions and calculations within your DQL SELECT statements to provide more granular control over your queries:
ClauseDescription
FROMThe required clause specifying the collection containing the documents for retrieval. (See FROM)
WHEREApplies filtering conditions to restrict the documents included in the result set. (See WHERE)
GROUP BYGroups documents based on one or more expressions for aggregate calculations. (See GROUP BY)
HAVINGFilters grouped results based on aggregate conditions. (See HAVING)
ORDER BYSpecifies the sorting order of the result set based on one or more expressions. (See ORDER BY)
LIMITRestricts the number of documents included in the result set. (See LIMIT)
OFFSETSkips a specific number of documents before returning the result set. (See OFFSET)

FROM

Required in each SELECT statement you write in DQL, the FROM element identifies the collection for document retrieval.
DQL
SELECT *
FROM your_collection_name
For example, a SELECT statement querying documents from the cars collection:
DQL
SELECT * FROM cars

WHERE

The WHERE clause filters data based on either an expression or a set of conditions that narrow the result set returned to you:
DQL
SELECT *
FROM your_collection_name
WHERE [condition]
For example, here is a basic SELECT statement querying documents from the cars collection based on a given address:
DQL
SELECT * FROM cars
WHERE location.address = '123 Main St, San Francisco, CA 98105'
To demonstrate a more complex query, here is a SELECT statement that queries using multiple expressions and logical operators to further refine the criteria for document retrieval:
DQL

SELECT * FROM cars
WHERE color = 'blue' AND features.trim = 'Standard' OR features.mileage > 10000

ORDER BY

With the ORDER BY clause, if you’d like, you can integrate calculations or expressions in your SELECT statement. Then sort the resulting documents to return in either ascending (ASC) or descending (DESC) alphabetical order:
DQL
SELECT *
FROM your_collection_name
ORDER BY expression_1, expression_2, ... [ASC|DESC]
For example, here is a simple SELECT statement that uses the ORDER BY clause to query and sort documents from the cars collection in descending (DESC) alphabetical order based on the field value set for the color property:
DQL
SELECT *
FROM cars ORDER BY color DESC
In this syntax:
  • your_collection_name is the name of the collection from which you want to retrieve the data.
  • expression_1, expression_2, ... are the expressions evaluated to sort the result. Expressions are resolved in order.
  • [ASC|DESC] is an optional parameter that specifies the sort order. If omitted, the default sort order is ascending (ASC). To sort in descending order, you can specify DESC.
Example In this example, the result set from the query will be sorted in descending order based on the values in the field:
DQL
-- Sort by a given field_name
SELECT *
FROM your_collection_name
ORDER BY field_name DESC
For instance, here "blue" cars return first and other cars sort by the natural order in the collection:
DQL
SELECT *
FROM cars
ORDER BY color = 'blue'

Sort Order by Object Type

In DQL, the hierarchy for comparing and sorting objects varies based on the following criteria:
  • If ascending (ASC) order operations:
    • boolean
    • number
    • binary
    • string
    • array
    • object
    • null
    • missing
  • If descending (DESC) order operations, sorting order is reversed:
    • missing
    • null
    • object
    • array
    • string
    • binary
    • number
    • boolean
  • If evaluating values, true results are prioritized and ordered first followed by false results.

Expressing Sort Order

Unless explicitly defined as DESC in your query, Ditto defaults to sorting in ascending (ASC). So, if you want to sort in ascending order, you do not have to express that in your query.

LIMIT

The LIMIT clause is used to restrict the number of documents returned by a query, allowing you to specify a maximum limit on the number of documents to be included in the result set:
DQL
SELECT *
FROM your_collection_name
LIMIT limit_value
In this syntax:
  • your_collection_name is the name of the collection from which you want to retrieve the data.
  • limit_value is the maximum number of documents you want to include in the result set.
For example, only return the first 10 documents from the your_collection_name collection:
DQL
SELECT *
FROM your_collection_name
LIMIT 10

OFFSET

The OFFSET clause is used to specify the number of records to skip before starting to return documents from the query result:
DQL
SELECT *
FROM your_collection_name
OFFSET number_of_items_to_skip
In this syntax:
  • your_collection_name is the name of the collection from which you want to retrieve the data.
  • number_of_items_to_skip is the number of items before returning the result set.
Using OFFSET with LIMIT is a common way to utilize OFFSET; for example:
DQL
SELECT *
FROM your_collection_name
LIMIT 10
OFFSET 10

Projections

Projections allow you to specify exactly which fields or calculated values to return from your query, rather than returning entire documents. This reduces data transfer and processing overhead.

Basic Field Selection

Select specific fields from documents:
DQL
SELECT make, model, year FROM cars

Aliasing

Use aliases to rename fields in your results:
DQL
SELECT make AS manufacturer, model AS car_model FROM cars
Default aliases are assigned if not specified:
  • Simple field references use the field name as the alias
  • Expressions get aliases like ($1), ($2), etc.

Calculated Fields

Create new fields using expressions:
DQL
SELECT make, model, price * 0.9 AS discounted_price FROM cars

DISTINCT Results

Remove duplicate rows from your results:
DQL
SELECT DISTINCT color FROM cars
When using DISTINCT, all projections are buffered in memory to enforce uniqueness, which increases memory requirements. Avoid using DISTINCT when the _id field is included, as results are already unique.

Aggregate Functions

Aggregate functions perform calculations across multiple documents and return a single result. DQL supports the following aggregate functions:

COUNT

Count documents or non-null values:
DQL
-- Count all documents
SELECT COUNT(*) FROM cars

-- Count non-null values in a field
SELECT COUNT(color) FROM cars

-- Count distinct values
SELECT COUNT(DISTINCT color) FROM cars

SUM

Calculate the sum of numeric values:
DQL
SELECT SUM(price) AS total_value FROM cars

-- Sum distinct values only
SELECT SUM(DISTINCT price) FROM cars

AVG

Calculate the average of numeric values:
DQL
SELECT AVG(mileage) AS average_mileage FROM cars

MIN and MAX

Find minimum and maximum values:
DQL
SELECT MIN(year) AS oldest_year, MAX(year) AS newest_year FROM cars

Combining Aggregates

Use multiple aggregate functions in a single query:
DQL
SELECT 
  COUNT(*) AS total_cars,
  AVG(price) AS avg_price,
  MIN(price) AS lowest_price,
  MAX(price) AS highest_price
FROM cars

GROUP BY

Group results and calculate aggregates for each group:
DQL
SELECT color, COUNT(*) AS count, AVG(price) AS avg_price 
FROM cars 
GROUP BY color
When using aggregates with GROUP BY, non-aggregate projections must be part of the GROUP BY clause:
DQL
-- This works: 'make' is in GROUP BY
SELECT make, COUNT(*) AS model_count 
FROM cars 
GROUP BY make

-- This would fail: 'model' is not in GROUP BY
-- SELECT make, model, COUNT(*) FROM cars GROUP BY make

HAVING

Filter grouped results based on aggregate values:
DQL
SELECT color, COUNT(*) AS count 
FROM cars 
GROUP BY color 
HAVING COUNT(*) > 5
Aggregate functions form a “dam” in the execution pipeline - all documents must be processed before results can be returned. This differs from non-aggregate queries which can stream results.