> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.

# IDs, Paths, Strings, and Keywords

This article provides reference information for the following:

<CardGroup>
  <Card title="Identifiers" icon="square-1" href="/dql/ids-paths-strings-keywords#identifiers" iconType="solid" horizontal />

  <Card title="Path Expressions" icon="square-2" href="/dql/ids-paths-strings-keywords#path-expressions" iconType="solid" horizontal />

  <Card title="String Literals" icon="square-3" href="/dql/ids-paths-strings-keywords#string-literals" iconType="solid" horizontal />

  <Card title="Comments" icon="square-4" href="/dql/ids-paths-strings-keywords#comments" iconType="solid" horizontal />

  <Card title="Reserved Keywords" icon="square-5" href="/dql/ids-paths-strings-keywords#reserved-keyword" iconType="solid" horizontal />
</CardGroup>

## Identifiers

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

### **Rules for Identifiers**

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

### **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. |

See also: [Reserved Keywords](#reserved-keywords)

## 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:

<Warning>
  In 4.11+ and `DQL_STRICT_MODE=false`, collection definitions are no longer required.

  [Read more](/dql/strict-mode)
</Warning>

<CodeGroup>
  ```sql 4.11+ theme={null}
  SELECT * FROM cars
  WHERE features.seats = 'leather'
  ```

  ```sql <=4.10 theme={null}
  SELECT * FROM COLLECTION cars (features MAP)
  WHERE features.seats = 'leather'
  ```
</CodeGroup>

```json JSON theme={null}
{
  "_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`:<ul><li>`a` is the leftmost name</li><li>`b` is an intermediate name</li><li>`c` is the rightmost name</li></ul>                                                                                                           |
| Collections     | Collection names or aliases can be present in path expressions to fully qualify them.<br />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:**

```sql DQL theme={null}
-- 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

<Note>
  For more information on working with attachments, see [Working with Attachments](/sdk/latest/crud/working-with-attachments).
</Note>

## String Literals

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

<Warning>
  Prior to version 5.0 double quotes (`"`) cannot be used to represent `string` literals within DQL.
</Warning>

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

```sql DQL theme={null}
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:**

```sql DQL theme={null}
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:**

```sql DQL theme={null}
SELECT * FROM cars WHERE features = {"seats": "leather", "sunroof": true}
```

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

## 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:

```sql DQL theme={null}
SELECT * FROM cars WHERE color = :color AND year > :minYear
```

### Parameter Names

Parameter names follow the same rules as [identifiers](#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

```sql DQL theme={null}
-- 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:

```sql DQL theme={null}
-- 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 `*/`:

```sql DQL theme={null}
/*
 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

```sql DQL theme={null}
/* Outer comment
   /* Nested comment */
   Still in outer comment
   /* Another nested comment */
   End of outer comment
*/
```

**Example with nested comments:**

```sql DQL theme={null}
/*
**  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/directives):

```sql DQL theme={null}
/*+ {"#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](#rules-for-identifiers))

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

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

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

### Previously reserved keywords

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

<Columns cols={3}>
  <Card>
    AGAINST <br />
    ANALYZE <br />
    ARRAY\_AGG <br />
    ATTACHMENT <br />
    BIGINT <br />
    BLOB <br />
    BYTEA <br />
    CEIL <br />
    CENTURY <br />
    CHAR <br />
    CHARACTER <br />
    CHARACTERS <br />
    CLOB <br />
    CLUSTER <br />
    COLLATE <br />
    CONFLICT <br />
    COUNT <br />
    COUNTER <br />
    CURRENT\_DATE <br />
    CURRENT\_TIME <br />
    CURRENT\_TIMESTAMP <br />
    DATE <br />
    DATETIME <br />
    DAY <br />
    DEC <br />
    DECADE <br />
    DECIMAL <br />
    DOUBLE <br />
    DOW <br />
    DOY <br />
    ENUM <br />
    EPOCH <br />
    ERROR <br />
    EXPANSION <br />
    EXTRACT <br />
  </Card>

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

  <Card>
    PRECISION <br />
    QUALIFY <br />
    QUARTER <br />
    QUERY <br />
    REAL <br />
    REGCLASS <br />
    REGISTER <br />
    SAFE\_CAST <br />
    SECOND <br />
    SMALLINT <br />
    STCOUNTER <br />
    TEXT <br />
    TIES <br />
    TIME <br />
    TIMESTAMP <br />
    TIMESTAMPTZ <br />
    TIMETZ <br />
    TIMEZONE <br />
    TIMEZONE\_HOUR <br />
    TIMEZONE\_MINUTE <br />
    TINYINT <br />
    TOP <br />
    TRUNCATE <br />
    TRY\_CAST <br />
    UNLOGGED <br />
    UNSIGNED <br />
    UUID <br />
    VALID <br />
    VARBINARY <br />
    VARCHAR <br />
    VARYING <br />
    WEEK <br />
    WITHOUT <br />
    YEAR <br />
    ZONE <br />
  </Card>
</Columns>