Creating an index on ARRAY<STRING(MAX)> in Google Cloud Spanner

Question

I'm trying to create an index on the AlbumTokens column in my Google Cloud Spanner test database and I get a mysterious error referencing an index option that is not currently documented:

CREATE INDEX AlbumTokens 
ON Albums (
    AlbumTokens
)

>>> Index AlbumTokens references ARRAY AlbumTokens, but is not declared as DISTINCT_ARRAY_ELEMENT index.

Is it possible to do this? If so, how?

I'm using the sample schema with an ARRAY<STRING> column added on:

CREATE TABLE Singers (
    SingerId INT64 NOT NULL,
    FirstName STRING(1024),
    LastName STRING(1024),
    SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId)

CREATE TABLE Albums (
    SingerId INT64 NOT NULL,
    AlbumId INT64 NOT NULL,
    AlbumTitle STRING(MAX),
    AlbumTokens ARRAY<STRING(MAX)>,
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE

Answer 1

You can't create an index using an Array as a key:

Disallowed types

These cannot be of type ARRAY:

A table's key columns.

An index's key columns.

You can include the Array in the index via the STORING keyword to return the array without joining to the primary table, but you can't scan on i