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