IDs, Paths, Strings, and Keywords

This article provides reference information for the following:

Identifiers

Path Expressions

String Literals

Comments

Reserved Keywords

Identifiers

Identifiers are the names of collections, documents, fields, and other database objects.

Rules for Identifiers

Rule	Guidelines
Case-Sensitivity	Identifiers are always case-sensitive
	Examples: (these are distinct identifiers and do not refer to the same field) DOCUMENTNAME DocumentName documentname
Non-Quoted Identifiers	Must start with a letter (A-Z or a-z) or an underscore Can contain only letters, digits, and underscores Cannot use reserved keywords
	Examples: 1abc ab$c abc_4u _abc abc123
Quoted Identifiers	Quote identifiers using backticks (grave accent)(`)(U+0060) All valid UTF-8 encoded characters may be used Can contain reserved keywords Can be escaped inside existing delimiters
	Examples: `1abc` `ab$c` `αΑ Δ ωΩ` `a b`.c `a “b“ c` `from`

Reserved Identifiers

The following table provides an overview of the DQL-specific restrictions that supersede common identifier rules:

Element	Rule
Document Identifier	`_id` is a reserved identifier for the document ID. All documents must have an assigned document ID.
System Collection Identifiers	Double underscore (`__`) is a reserved identifier prefix for collections. Double underscore denotes a Ditto system collection.

Path Expressions

Since Ditto stores data as JSON-like documents, you can use keypath indexing, known commonly as dot syntax, to navigate and reference specific parts of a Ditto document when performing query operations in DQL. To separate a name in a path, use a dot (.). For example, to navigate to the seats field in a SELECT statement:

In 4.11+ and DQL_STRICT_MODE=false, collection definitions are no longer required.Read more

SELECT * FROM cars
WHERE features.seats = 'leather'

SELECT * FROM COLLECTION cars (features MAP)
WHERE features.seats = 'leather'

JSON

{
  "_id": "123abc",
  "year": 2018,
  "vin": 123456,
  "features": {
    "speakers": 5
    "seats": "leather"
    }
  }

Rules for Path Expressions

Following is an overview of the of the DQL-specific restrictions that supersede common rules:

Topic	Guidelines
Path Evaluation	Path expressions are evaluated from left to right. For example, `a.b.c`: `a` is the leftmost name `b` is an intermediate name `c` is the rightmost name
Collections	Collection names or aliases can be present in path expressions to fully qualify them. If absent, the collection is added during the statement formalisation process, for example in `SELECT a one, cars.a two FROM cars`, both projection elements `one` and `two` refer to the same field.
Types	Only `REGISTER` with nested JSON object and `MAP` types can be navigated with path expressions.
`MISSING`	The path expression result is `MISSING` if any element does not exist.

Attachment Path Expressions

Attachments in Ditto have a specific structure that can be navigated using path expressions.

Attachment Structure

An attachment field contains:

id: The attachment identifier
len: The length in bytes
metadata: Optional metadata object

Path Expression Rules for Attachments

When navigating into an ATTACHMENT field:

Path expressions can access id, len, and metadata fields
Accessing metadata returns the metadata object if present, or MISSING if not
Further navigation into metadata is allowed

Examples:

DQL

-- Access attachment ID
SELECT photo.id FROM profiles WHERE photo IS NOT MISSING

-- Filter by attachment size
SELECT * FROM documents WHERE file.len > 1000000

-- Access attachment metadata
SELECT attachment.metadata.filename FROM uploads

-- Navigate nested metadata
SELECT photo.metadata.dimensions.width FROM images

Attachment Errors

Certain operations on attachments will cause errors:

Attempting to navigate to fields other than id, len, or metadata
Using array subscripting on attachments
Treating attachments as arrays or regular objects

For more information on working with attachments, see Working with Attachments.

String Literals

In versions prior to version 5.0: Enclose string literals in single quotes ('') in DQL; for example 'blue'.

Prior to version 5.0 double quotes (") cannot be used to represent string literals within DQL.

Enclose string literals in single ('') or double ("") quotes in DQL; for example 'blue' or "blue":

DQL

SELECT * FROM cars WHERE color = 'blue' OR color = "red"

Escape Sequences

To include special characters in strings:

Single quote in single-quoted string: Use two single quotes ''
- Example: 'he''llo' represents the string he'llo
Escape sequences: Use the e prefix to enable common escape sequences
- Example: e"\"" represents a string containing a double quote
- Example: e"\n" represents a newline character
- Without the e prefix, "\n" is a string containing a backslash followed by n

Other Literals

Number Literals

Numbers can be specified as-is:

Decimal: 123, 45.67, -89.0
Hexadecimal: Use the 0x prefix, e.g., 0xFF, 0x1A2B

Boolean and NULL Literals

TRUE, FALSE, and NULL can be specified as-is
Case does not matter: true, True, TRUE are all equivalent

Array Literals

Array literals use standard JSON syntax with square brackets:

Empty array: []
Array with elements: [1, "a", true, null]

Example:

DQL

SELECT * FROM cars WHERE colors = ['blue', 'red']

Object Literals

Object literals use standard JSON syntax with curly braces:

Empty object: {}
Object with fields: {"a": 1, "b": true, "c": "text"}

Example:

DQL

SELECT * FROM cars WHERE features = {"seats": "leather", "sunroof": true}

Array and object literals are supported in SDK 4.8+. For earlier versions, use parameters to pass arrays and objects.

Parameters

To protect against SQL injection attacks, DQL supports parameterized queries. Parameters take the place of literals and are replaced with actual values at execution time.

Parameter Syntax

A parameter consists of a colon : followed by the parameter name:

DQL

SELECT * FROM cars WHERE color = :color AND year > :minYear

Parameter Names

Parameter names follow the same rules as identifiers:

Regular parameter names: :abc_4u, :foo, :id
Delimited parameter names use backtick-quoted identifiers:
:` 1bc` :`foo bar`
Parameter names are case-sensitive: :Color and :color are different

Where Parameters Are Supported

Parameters can be used in:

WHERE clauses
ORDER BY clauses
LIMIT clauses
OFFSET clauses
DOCUMENTS clause in INSERT
Mutators in UPDATE

Parameter Examples

DQL

-- WHERE clause with parameters
SELECT * FROM cars WHERE color = :color AND mileage < :maxMileage

-- INSERT with parameters
INSERT INTO cars DOCUMENTS (:newCar)

-- UPDATE with parameters
UPDATE cars SET price = :newPrice WHERE _id = :carId

-- ORDER BY and LIMIT with parameters
SELECT * FROM cars ORDER BY :sortField LIMIT :pageSize

Comments

DQL supports both inline and multi-line comments.

Inline Comments

Inline comments start with -- and continue to the end of the line:

DQL

-- This is an inline comment
SELECT * FROM cars -- Comments can appear at the end of lines

Multi-line Comments

Multi-line comments are enclosed by /* and */:

DQL

/*
 This is a multi-line comment
 that spans multiple lines
*/
SELECT * FROM cars

Nested Block Comments

Block comments can be nested. Within a block comment:

/* always starts a nested block comment
*/ always ends the innermost block comment
These symbols cannot be escaped

DQL

/* Outer comment
   /* Nested comment */
   Still in outer comment
   /* Another nested comment */
   End of outer comment
*/

Example with nested comments:

DQL

/*
**  If you put a /* inside
**  of a comment, be sure to
**  add a matching */
*/
SELECT * FROM cars

Directive Comments

Special comment syntax can be used to specify query directives:

DQL

/*+ {"#prefer_order": true, "#max_scans": 10} */
SELECT * FROM cars WHERE color = 'blue' ORDER BY year

--+ {"#prefer_order": true}
SELECT * FROM cars WHERE color = 'blue' ORDER BY year

Reserved Keywords

The following table provides a complete overview of the reserved keywords in DQL. Identifiers that are the same as a reserved keyword may still be used, but must be quoted (see: Rules for identifiers)

ALL
ALTER
AND
ANTI
ANY
APPLY
ARRAY
AS
ASC
AT
BETWEEN
BINARY
BOOLEAN
BOTH
BY
CASE
CAST
COLLECTION
CONTEXT
CREATE
CROSS
CURRENT
DEFAULT
DELETE
DESC
DISTINCT
DISTRIBUTE
DO
DOCUMENTS
DROP
ELEMENT
ELSE
END
ESCAPE
EVERY
EVICT
EXCEPT
EXISTS
EXPLAIN
FAIL
FALSE
FETCH
FILTER
FLOAT

FOLLOWING
FOR
FROM
FULL
GROUP
GROUPS
HAVING
ILIKE
IN
INCREMENT
INDEX
INITIAL
INNER
INSERT
INT
INTEGER
INTERSECT
INTO
IS
JOIN
LANGUAGE
LATERAL
LEADING
LEFT
LIKE
LIMIT
MERGE
MISSING
NOT
NOTHING
NULL
OBJECT
OFFSET
ON
OPERATOR
OR
ORDER
OUTER
OVER
PARTITION
PEERS
POSITION
PRECEDING
RANGE

RAW
RESET
RETURNING
RIGHT
ROW
ROWS
SELECT
SEMI
SET
SHOW
SIMILAR
SORT
STRING
SUBSTRING
SYNC
SYSTEM
TABLE
TEMP
TEMPORARY
THEN
TO
TOMBSTONE
TRAILING
TRIM
TRUE
UNBOUNDED
UNION
UNKNOWN
UNNEST
UNSET
UPDATE
UPDATE_LOCAL_DIFF
USE
USING
VALUE
VALUES
VIEW
WHEN
WHERE
WITH
WITHIN
XOR

Previously reserved keywords

The following keywords were reserved in earlier versions but are no longer reserved as of version 5.0:

AGAINST
ANALYZE
ARRAY_AGG
ATTACHMENT
BIGINT
BLOB
BYTEA
CEIL
CENTURY
CHAR
CHARACTER
CHARACTERS
CLOB
CLUSTER
COLLATE
CONFLICT
COUNT
COUNTER
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DATE
DATETIME
DAY
DEC
DECADE
DECIMAL
DOUBLE
DOW
DOY
ENUM
EPOCH
ERROR
EXPANSION
EXTRACT

FLOOR
HOUR
ID
INHERIT
INTERVAL
ISODOW
ISOYEAR
JULIAN
LARGE
LISTAGG
LOCALTIME
LOCALTIMESTAMP
MAP
MEDIUMINT
MICROSECOND
MICROSECONDS
MILLENIUM
MILLENNIUM
MILLISECOND
MILLISECONDS
MINUTE
MODE
MONTH
NANOSECOND
NANOSECONDS
NATURAL
NUMERIC
NVARCHAR
OCTETS
OUTPUTFORMAT
OVERFLOW
OVERLAY
PERCENT
PLACING
PNCOUNTER

PRECISION
QUALIFY
QUARTER
QUERY
REAL
REGCLASS
REGISTER
SAFE_CAST
SECOND
SMALLINT
STCOUNTER
TEXT
TIES
TIME
TIMESTAMP
TIMESTAMPTZ
TIMETZ
TIMEZONE
TIMEZONE_HOUR
TIMEZONE_MINUTE
TINYINT
TOP
TRUNCATE
TRY_CAST
UNLOGGED
UNSIGNED
UUID
VALID
VARBINARY
VARCHAR
VARYING
WEEK
WITHOUT
YEAR
ZONE

Statements

Types and Functions

Reference

IDs, Paths, Strings, and Keywords

Identifiers

Path Expressions

String Literals

Comments

Reserved Keywords

Identifiers

Rules for Identifiers

Reserved Identifiers

Path Expressions

Rules for Path Expressions

Attachment Path Expressions

Attachment Structure

Path Expression Rules for Attachments

Attachment Errors

String Literals

Escape Sequences

Other Literals

Number Literals

Boolean and NULL Literals

Array Literals

Object Literals

Parameters

Parameter Syntax

Parameter Names

Where Parameters Are Supported

Parameter Examples

Comments

Inline Comments

Multi-line Comments

Nested Block Comments

Directive Comments

Reserved Keywords

Previously reserved keywords

Identifiers

Path Expressions

String Literals

Comments

Reserved Keywords

​Identifiers

​Rules for Identifiers

​Reserved Identifiers

​Path Expressions

​Rules for Path Expressions

​Attachment Path Expressions

​Attachment Structure

​Path Expression Rules for Attachments

​Attachment Errors

​String Literals

​Escape Sequences

​Other Literals

​Number Literals

​Boolean and NULL Literals

​Array Literals

​Object Literals

​Parameters

​Parameter Syntax

​Parameter Names

​Where Parameters Are Supported

​Parameter Examples

​Comments

​Inline Comments

​Multi-line Comments

​Nested Block Comments

​Directive Comments

​Reserved Keywords

​Previously reserved keywords

Identifiers

Rules for Identifiers

Reserved Identifiers

Path Expressions

Rules for Path Expressions

Attachment Path Expressions

Attachment Structure

Path Expression Rules for Attachments

Attachment Errors

String Literals

Escape Sequences

Other Literals

Number Literals

Boolean and NULL Literals

Array Literals

Object Literals

Parameters

Parameter Syntax

Parameter Names

Where Parameters Are Supported

Parameter Examples

Comments

Inline Comments

Multi-line Comments

Nested Block Comments

Directive Comments

Reserved Keywords

Previously reserved keywords