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

RuleGuidelinesExamples
Case-SensitivityIdentifiers are always case-sensitive* DOCUMENTNAME

* DocumentName

* documentname
Non-Quoted IdentifiersMust start with a letter (A-Z or a-z) or an underscore

Can contain only letters, digits, and underscores

INVALID: Cannot use reserved keywords; for example:

* 1abc

* ab$c		* abc_4u

* _abc

* abc123
Quoted IdentifiersQuote identifiers using backticks (“)

Usage of double quotes ("") is deprecated

Use any UTF-8 character

Can be escaped inside existing delimiters		* `1abc`

* `ab$c`

* `a b`

* `a b`.c

* `a “b“ c`

​
Reserved Identifiers

The following table provides an overview of the DQL-specific restrictions that supersede common identifier rules:
ElementRule
Document Identifier_id is a reserved identifier for the document ID. All documents must have an assigned document ID.
System Collection IdentifiersDouble 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'
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:
TopicGuidelines
Path EvaluationPath 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
CollectionsCollection names cannot be present in path expressions.
TypesOnly REGISTER with nested JSON object and MAP types can be navigated with path expressions.
MISSINGThe path expression result is MISSING for the following conditions are both true:

* The leftmost name and all intermediate names are a REGISTER with JSON object type or a MAP type.

* The last name in the path does not exist.

​
String Literals

Enclose string literals in single quotes ('') in DQL; for example 'blue' :
DQL
SELECT * FROM cars WHERE color = 'blue'
Double quotes ("") cannot be not used to represent string literals within DQL.

​
Comments

DQL supports both inline and multi-line comments.

​
Inline Comments

Inline comments are made using the -- operator.
DQL
-- This is an inline comment

​
Multi-line Comments

Multi-line comments are inclosed by the /* and */operator.
DQL
/*
 This is a multi-line comment
*/

​
Reserved Keyword

The following table provides a complete overview of the reserved keywords in DQL:

AGAINST
ALL
ALTER
ANALYZE
AND
ANTI
ANY
APPLY
ARRAY
ARRAY_AGG
AS
ASC
AT
ATTACHMENT
BETWEEN
BIGINT
BINARY
BLOB
BOOLEAN
BOTH
BY
BYTEA
CASE
CAST
CEIL
CENTURY
CHAR
CHARACTER
CHARACTERS
CLOB
CLUSTER
COLLATE
COLLECTION
CONFLICT
CONTEXT
COUNT
COUNTER
CROSS
CURRENT
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DATE
DATETIME
DAY
DEC
DECADE
DECIMAL
DEFAULT
DELETE
DESC
DISTINCT
DISTRIBUTE
DO
DOCUMENTS
DOUBLE
DOW
DOY
ELEMENT
ELSE
END
ENUM
EPOCH
ERROR
ESCAPE
EVICT
EXCEPT
EXISTS
EXPANSION
EXPLAIN
EXTRACT
FAIL
FALSE
FETCH
FILTER
FLOAT
FLOOR
FOLLOWING
FOR
FROM
FULL
GROUP
GROUPS
HAVING
HOUR
ID
ILIKE
IN
INCREMENT
INHERIT
INITIAL
INNER
INSERT
INT
INTEGER
INTERSECT
INTERVAL
INTO
IS
ISODOW
ISOYEAR
JOIN
JULIAN
LANGUAGE
LARGE
LATERAL
LEADING
LEFT
LIKE
LIMIT
LISTAGG
LOCALTIME
LOCALTIMESTAMP

MAP
MEDIUMINT
MICROSECOND
MICROSECONDS
MILLENIUM
MILLENNIUM
MILLISECOND
MILLISECONDS
MINUTE
MISSING
MODE
MONTH
NANOSECOND
NANOSECONDS
NATURAL
NOT
NOTHING
NULL
NUMERIC
NVARCHAR
OBJECT
OCTETS
OFFSET
ON
OPERATOR
OR
ORDER
OUTER
OUTPUTFORMAT
OVER
OVERFLOW
OVERLAY
PARTITION
PEERS
PERCENT
PLACING
PNCOUNTER
POSITION
PRECEDING
PRECISION
QUALIFY
QUARTER
QUERY
RANGE
RAW
REAL
REGCLASS
REGISTER
RESET
RETURNING
RIGHT
ROW
ROWS
SAFE_CAST
SECOND
SELECT
SEMI
SET
SHOW
SIMILAR
SMALLINT
SORT
STCOUNTER
STRING
SUBSTRING
SYNC
SYSTEM
TABLE
TEMP
TEMPORARY
TEXT
THEN
TIES
TIME
TIMESTAMP
TIMESTAMPTZ
TIMETZ
TIMEZONE
TIMEZONE_HOUR
TIMEZONE_MINUTE
TINYINT
TO
TOMBSTONE
TOP
TRAILING
TRIM
TRUE
TRUNCATE
TRY_CAST
UNBOUNDED
UNION
UNKNOWN
UNLOGGED
UNNEST
UNSIGNED
UPDATE
USING
UUID
VALID
VALUE
VALUES
VARBINARY
VARCHAR
VARYING
VIEW
WEEK
WHEN
WHERE
WITH
WITHIN
WITHOUT
XOR
YEAR
ZONE