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 yourSELECT
statements:
DQL
projection
can be:
*
- Returns all fields from the documentsfield1, field2, ...
- Returns specific fieldsexpression AS alias
- Returns calculated values with custom names- Aggregate functions like
COUNT(*)
,SUM(field)
, etc.
cars
collection WHERE
the color
property is set to the value ‘blue’ :
DQL
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 DQLSELECT
statements to provide more granular control over your queries:
Clause | Description |
---|---|
FROM | The required clause specifying the collection containing the documents for retrieval. (See FROM) |
WHERE | Applies filtering conditions to restrict the documents included in the result set. (See WHERE) |
GROUP BY | Groups documents based on one or more expressions for aggregate calculations. (See GROUP BY) |
HAVING | Filters grouped results based on aggregate conditions. (See HAVING) |
ORDER BY | Specifies the sorting order of the result set based on one or more expressions. (See ORDER BY) |
LIMIT | Restricts the number of documents included in the result set. (See LIMIT) |
OFFSET | Skips a specific number of documents before returning the result set. (See OFFSET) |
FROM
Required in eachSELECT
statement you write in DQL, the FROM
element identifies the collection for document retrieval.
DQL
SELECT
statement querying documents from the cars
collection:
DQL
WHERE
TheWHERE
clause filters data based on either an expression or a set of conditions that narrow the result set returned to you:
DQL
SELECT
statement querying documents from the cars
collection based on a given address:
DQL
SELECT
statement that queries using multiple expressions and logical operators to further refine the criteria for document retrieval:
DQL
ORDER BY
With theORDER 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
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
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 specifyDESC
.
DQL
"blue"
cars return first and other cars sort by the natural order in the collection:
DQL
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 byfalse
results.
Expressing Sort Order
Unless explicitly defined asDESC
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
TheLIMIT
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
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.
your_collection_name
collection:
DQL
OFFSET
TheOFFSET
clause is used to specify the number of records to skip before starting to return documents from the query result:
DQL
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.
OFFSET
with LIMIT
is a common way to utilize OFFSET
; for example:
DQL
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
Aliasing
Use aliases to rename fields in your results:DQL
- 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
DISTINCT Results
Remove duplicate rows from your results:DQL
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
SUM
Calculate the sum of numeric values:DQL
AVG
Calculate the average of numeric values:DQL
MIN and MAX
Find minimum and maximum values:DQL
Combining Aggregates
Use multiple aggregate functions in a single query:DQL
GROUP BY
Group results and calculate aggregates for each group:DQL
GROUP BY
, non-aggregate projections must be part of the GROUP BY
clause:
DQL
HAVING
Filter grouped results based on aggregate values:DQL
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.