Simplified Dot Notation Syntax for JSON Index Creation

Traditionally, creating indexes on JSON data required complex and verbose SQL/JSON syntax with detailed error handling specifications, making the process challenging and susceptible to errors.

The new simplified dot notation syntax addresses these issues by allowing users to specify exactly which JSON fields to index using intuitive, familiar dot notation, eliminating the need to define underlying technical details. This streamlined approach makes index definitions more readable, faster to implement, and less prone to mistakes. The simplified syntax applies to both multi-value indexes, which index array or repeated fields, and single-value indexes, which index scalar fields. Existing index creation syntax remains fully supported.

Syntax

A JSON index using dot notation can be created with the following general form:

CREATE JSON [UNIQUE] [SPARSE | DENSE] [SINGLEVALUE | MULTIVALUE]
INDEX index_name ON table_name [table_alias]
(dot_path [ASC | DESC], [dot_path [ASC | DESC]] ... )
[index_properties];

Parameters and Options

Parameter	Description
`JSON`	Mandatory keyword that triggers use of the simplified JSON index syntax.
`UNIQUE`	Optional parameter that enforces uniqueness across the specified indexed fields.
`SPARSE` / `DENSE`	Optional parameter. `SPARSE` indicates the index uses a null on empty handler. `DENSE` indicates the index uses a present on empty handler. If neither keyword is specified, `SPARSE` is used by default.
`SINGLEVALUE` / `MULTIVALUE`	Optional parameter. `SINGLEVALUE` creates an index for scalar fields, `MULTIVALUE` creates an index for arrays/repeats (default).
`INDEX index_name`	Required parameter that specifies the name of the index to create.
`ON table_name [alias]`	Required parameter. Name (and optional alias) of the table containing the JSON column to be indexed.
`dot_path [ASC\|DESC]`	One or more dot-separated paths specifying the fields to index. You may specify ascending (`ASC`) or descending (`DESC`) order for each field.
`index_properties`	Optional parameter specifying additional index properties such as `ONLINE` for online creation.

Note:

If neither SPARSE nor DENSE is specified, SPARSE behavior is used by default.
In composite indexes, all fields must reference the same JSON column on the specified table.
Only simple dot notation is supported; general SQL expressions are not permitted in dot paths.
The simplified syntax is not supported for BITMAP indexes on multi-value indexes.

Example 30-17 Indexing an Array or Scalar Field

The simplified syntax allows direct indexing of either scalar fields or array elements within JSON data. For array fields, use the field’s path as it appears in the document.

Indexing an Array Field

CREATE JSON INDEX idx_purchased_arr ON mytab t (
  t.data.parts.purchased
);

This example creates a multi-value index on the purchased array within the parts object, enabling fast queries on any element within the array.

Indexing a Scalar Field

CREATE JSON SINGLEVALUE INDEX idx_count_scalar ON mytab t (
  t.data.parts.count
);

This statement creates a single-value index on the count field for each parts object, optimizing lookups based on this scalar value.

Example 30-18 Creating a Composite Multi-Value or Single-Value Index

Composite indexes allow you to index multiple fields (array or scalar) within the same JSON column, enhancing performance for queries filtering on combinations of those fields.

Composite Multi-Value Index

CREATE JSON INDEX idx_composite_mvi ON mytab t (
  t.data.parts.purchased,
  t.data.name,
  t.data.parts.count
);

This index is created on three fields, supporting efficient searches involving any or all fields, even when fields may contain arrays.

Composite Single-Value Index

CREATE JSON SINGLEVALUE INDEX idx_composite_svi ON mytab t (
  t.data.parts.purchased,
  t.data.name,
  t.data.parts.count
);

This example creates a single-value index for the same three fields, useful when each field contains a singular, scalar value in every document.

Example 30-19 Composite Multi-Value or Single-Value Index with Ordering

You may optionally specify sort order (ascending or descending) for each indexed field, which can improve range or ordered query performance.

Composite Multi-Value Index with Ordering

CREATE JSON INDEX idx_composite_ordered_mvi ON mytab t (
  t.data.parts.purchased DESC,
  t.data.name ASC,
  t.data.parts.count DESC
);

This creates a multi-value index with descending order on purchased and count, and ascending order on name.

Composite Single-Value Index with Ordering

CREATE JSON SINGLEVALUE INDEX idx_composite_ordered_svi ON mytab t (
  t.data.parts.purchased DESC,
  t.data.name ASC,
  t.data.parts.count DESC
);

Similarly, this single-value index enforces the specified ordering for optimized access.

Example 30-20 Index with Explicit Item Methods for Type Enforcement

Item methods may be appended to a field path to enforce the data type of indexed values. This results in more efficient queries and ensures consistency.

CREATE JSON INDEX idx_type_enforced ON mytab t (
  t.data.parts.purchased,
  t.data.name.string(),
  t.data.parts.count.number()
);

This index enforces that name values are indexed as strings and count values as numbers.

Example 30-21 Unique and Dense Multi-Value or Single-Value Index

Use the UNIQUE keyword to enforce uniqueness on combinations of indexed values, and the DENSE keyword to ensure index entries are present even when a field is absent from some documents.

Unique, Dense Multi-Value Index

CREATE JSON UNIQUE DENSE INDEX idx_unique_dense_mvi ON mytab t (
  t.data.parts.purchased,
  t.data.name,
  t.data.parts.count
);

This creates a multi-value index that enforces uniqueness across all three fields and includes entries where fields might be missing (DENSE).

Unique, Sparse Single-Value Index

CREATE JSON UNIQUE SPARSE SINGLEVALUE INDEX idx_unique_sparse_svi ON mytab t (
  t.data.parts.purchased
);

This defines a single-value index on purchased that enforces uniqueness and includes only rows where the field is present (SPARSE is the default).