CREATE INDEX Statements
- Capella Columnar
- reference
This topic describes how to use a CREATE statement to create an index on a remote or standalone collection.
Syntax
CreateIndex EBNF
EBNFCreateIndex ::= "CREATE" "ANALYTICS"? "INDEX" Identifier ("IF" "NOT" "EXISTS")?
"ON" QualifiedName
"(" IndexElement ( "," IndexElement )* ")"
IndexUnknown?
IndexCastDefault?
CreateIndex Diagram
Show IndexElement Diagram
Show ArrayIndexElement Diagram
Show IndexField Diagram
Show NestedField Diagram
Show IndexTypeRef Diagram
Show IndexUnknown Diagram
Examples
The following example creates standard indexes on the identified fields for the specified types.
SQL++ CREATE INDEX song_title_idx on music.myPlaylist.countrySongs (title: string);
CREATE INDEX artist_name_idx on music.myPlaylist.countrySongs (name: string);
CREATE INDEX release_date_idx on music.myPlaylist.countrySongs (release_date: date);
Show additional examples
The following example creates an array index on an array of primitive data types—in this case, an array of strings.
SQL++ CREATE INDEX countrySongLikesIdx
ON music.myPlaylist.countrySongs (UNNEST public_likes: string)
EXCLUDE UNKNOWN KEY;
The following example creates an array index on two nested integer fields in an array of objects.
SQL++ CREATE INDEX myPlaylistSongsRatingsIdx
ON music.myPlaylist.countrySongs (UNNEST reviews
SELECT ratings.Lyrics: bigint,
ratings.Instrumentals: bigint)
EXCLUDE UNKNOWN KEY;
The following example creates an array index on one string field which is outside of an array and two nested integer fields in a nested array of objects.
SQL++ CREATE INDEX myPlaylistArtistSongsRatingsIdx
ON music.myPlaylist.countrySongs (artist: string,
UNNEST reviews
SELECT ratings.Lyrics: bigint,
ratings.Instrumentals: bigint)
EXCLUDE UNKNOWN KEY;
Arguments
- Identifier
-
For the
CREATE INDEXstatement, the firstIdentifieris the index name.
- QualifiedName
-
The
QualifiedNameidentifies the collection to build the index on.
- Index Definition
-
The index definition is contained within brackets, and consists of a list of index elements. Within the index definition, each
IndexElementcan be either anArrayIndexElement, or anIndexField. If the index definition contains one or more array index elements, Capella Columnar creates an array index. If the index definition contains only index fields, it creates a standard, non-array index. To eliminate ambiguity, you can parenthesize theIndexElementitself, as in cases where the index definition contains both array index elements and index fields.
- UNNEST
-
Within an
ArrayIndexElement, the UNNEST keyword denotes a field or nested field that contains an array, and the SELECT denotes one or more fields or nested fields inside an object of the aforementioned array. If an array contains only primitive types—strings, integers, etc.—then you specify only UNNEST. Each indexed field in the array index element must have a type identifier.
- IndexField
-
The
IndexFieldconsists of aNestedFieldthat specifies a field path into the indexed JSON document, and a type identifier.
- IndexUnknown
-
The
IndexUnknownmodifier enables you to specify whether you want the system to make an entry or not in a standard, non-array index when the indexed key’s value is NULL or MISSING. The following table outlines the behavior of INCLUDE UNKNOWN KEY and EXCLUDE UNKNOWN KEY:
| Modifier | All keys NULL or MISSING | Some keys NULL or MISSING | No NULL or MISSING |
|---|---|---|---|
INCLUDE UNKNOWN KEY |
Entry included ✓ |
Entry included ✓ |
Entry included ✓ |
EXCLUDE UNKNOWN KEY |
Entry excluded ✗ |
Entry included ✓ |
Entry included ✓ |
If you omit this modifier, then the default is INCLUDE UNKNOWN KEY.
| Array indexes cannot include NULL or MISSING values. For this reason, you must specify EXCLUDE UNKNOWN KEY when defining an array index. |