Support for ARRAY data types in Spanner targets

This topic explains how to write native Cloud Spanner array columns from array values that arrive as Java collections, Java arrays, JDBC arrays, or string notation. The writer converts every element to the element type of the target array column.

Supported array types

Dialect	Supported target array types	Notes
GoogleSQL	ARRAY<BOOL>, ARRAY<INT64>, ARRAY<FLOAT32>, ARRAY<FLOAT64>, ARRAY<STRING>, ARRAY<BYTES>, ARRAY<DATE>, ARRAY<TIMESTAMP>, ARRAY<NUMERIC>, ARRAY<JSON>	For target DDL, ARRAY<STRING(MAX)>, ARRAY<STRING(2000)>, and ARRAY<STRING(1)> are supported. The writer uses the same ARRAY<STRING> converter for all of them. For ARRAY<BYTES>, string-form elements must be hexadecimal or Base64; hexadecimal is attempted first.
PostgreSQL	boolean[], int8[], float4[], float8[], varchar[], bytea[], date[], timestamptz[], numeric[], jsonb[]	Aliases: int8[]: bigint[], int[], integer[]; float4[]: real[]; float8[]: double precision[]; varchar[]: character varying[], character varying(N)[], text[]; timestamptz[]: timestamp with time zone[]; numeric[]: decimal[]. bytea[] string elements must be hexadecimal or Base64; hexadecimal is attempted first.

Before you begin

Create each native ARRAY column manually in the target table before starting the application.
Do not use an ARRAY column writer KeyColumns value.
If you add, remove, or change an ARRAY column, follow the schema-change procedure in Schema and DDL handling.

How the writer receives array values

Input form	Supported values and behavior
Native array value	java.sql.Array; java.lang.String; any Iterable, including List, Set, and Queue; and any array type, including primitive arrays, Object[], byte[][], and ByteArray[] values. byte[] and com.google.cloud.ByteArray values are supported as BYTES elements. The behavior is the same for both writers.
String notation	A comma-separated list enclosed in {...} or [...]. Most sources emit this form. Oracle can emit java.sql.Array. Use a continuous query or Open Processor when you need to emit another supported native form.

A Set or Queue is allowed. The writer uses the collection's iteration order. Use an ordered collection, such as LinkedHashSet, if element order matters.

String notation

Enclose the complete list in braces ({...}) or square brackets ([...]) and separate top-level elements with commas. Quote an element with double quotation marks when it contains a comma, bracket, leading or trailing whitespace, or a quotation mark. Within a quoted element, use \" for a literal quotation mark and \\ for a literal backslash.

Parsing rules

The complete value must be enclosed in {...} or [...].
Commas, brackets, and whitespace inside a quoted element are literal content, not delimiters. A quote can begin an element only at an element boundary.
Inside a quoted top-level element, \" becomes a literal quotation mark and \\ becomes a literal backslash. Other sequences, such as \n or \t, remain literal characters.
A nested {...} or [...] payload is read as one element. The parser preserves its commas, quotation marks, brackets, backslashes, and whitespace for the target JSON parser.
Unquoted NULL, null, and Null represent a SQL null element. Quoted "null" is a literal string only for STRING, character varying, JSON, and JSONB arrays.

Examples of supported String values:

Input	Meaning
{1,2,3} or [1,2,3]	Three integer elements.
{true,NULL,false}	A Boolean array with a null middle element.
{} or []	An empty array with zero elements.
{"hello, world","foo"}	Two string elements; the comma in the first value is preserved.
{unquoted,"with,comma",another}	Bare and quoted string elements can be mixed.
{616263,YWJj}	Two BYTES values encoded as hexadecimal and Base64.
[{"a":1},{"b":2}]	Two JSON or JSONB objects.
{{"a":1,"b":2}}	One JSON or JSONB object that contains an internal comma.

Rejected string inputs

The following strings are rejected. Correct the value before resuming the application.

Input	Reason
1,2,3	The value is not enclosed in {...} or [...].
(1,2,3)	Parentheses are not recognized as array delimiters.
{1,,3}	Consecutive commas create a missing element.
{1,2,}	A trailing comma creates a missing element.
{,1,2}	A leading comma creates a missing element.
{ab"cd}	A quotation mark appears in the middle of an unquoted element.
{"foo}	The quoted element is not closed.
{a\	The input ends with a trailing backslash.
{1,2]	The outer brackets do not match.
{"a"b,"c"}	Characters appear after a closing quotation mark before the next comma.
{"a":1,"b":2}	This is a JSON object, not an array of JSON values. Add an outer array wrapper, for example {{"a":1,"b":2}}.

Element formats

Target element type	Accepted input and important rules
ARRAY<BOOL> / boolean[]	String tokens: true, false, on, off, yes, no, 1, and 0, case-insensitive. Other string tokens halt processing. For native numeric values, only 0 and 1 are safe; a native floating value is truncated to an integer before the Boolean check.
ARRAY<INT64> / int8[], bigint[], int[], integer[]	Signed integer in range, for example 1 or -42. Decimal, scientific, and hexadecimal notation are not supported.
ARRAY<FLOAT32> / float4[], real[]	Decimal or scientific notation with an optional sign. NaN and Infinity are accepted.
ARRAY<FLOAT64> / float8[], double precision[]	Decimal or scientific notation with an optional sign. NaN and Infinity are accepted.
ARRAY<NUMERIC> / numeric[], decimal[]	Decimal or scientific notation with an optional sign. NaN and Infinity are not accepted.
ARRAY<STRING> / varchar[], character varying[], text[]	A bare or quoted string. Whitespace is retained. Escape embedded quotation marks and backslashes inside quoted values.
ARRAY<BYTES> / bytea[]	Hexadecimal, for example 616263, or Base64, for example YWJj. The writer attempts hexadecimal decoding first. An empty quoted string writes an empty byte array.
ARRAY<DATE> / date[]	ISO date in YYYY-MM-DD form, for example 2024-01-01.
ARRAY<TIMESTAMP> / timestamptz[], timestamp with time zone[]	ISO 8601 date-time with or without an offset. A value without a time-zone designator is interpreted as UTC. Sub-second precision up to nanoseconds is preserved. Quote a value only when it contains a space.
ARRAY<JSON> / jsonb[]	Bare primitives are accepted. Objects and arrays that contain commas are accepted when nested under the outer array wrapper or quoted with escaped internals. The writer passes the value to Spanner; Spanner validates JSON syntax when it applies the mutation.

Null and empty arrays

Scenario	Behavior
The entire source-column value is null	The writer stores a null array.
An unquoted NULL, null, or Null element	The writer stores a null element. SQL null elements are supported for every supported target array type.
{} or []	The writer stores an empty array with zero elements.
Quoted "null" or empty quoted element for STRING, character varying, JSON, or JSONB	The writer stores the literal string "null" or an empty string.
Quoted "null" or empty quoted element for Boolean, numeric, date, or timestamp	The value is rejected and processing halts.
Quoted "null" for BYTES	Both hexadecimal and Base64 decoding fail and processing halts.
Empty quoted element for BYTES	The writer stores an empty byte array.

Validation and troubleshooting

Symptom	Likely cause and action
The application halts when processing an array value	Verify the outer wrapper, quotation marks, brackets, commas, and element types. A malformed string, element-conversion failure, or unsupported native input type stops the application.
The target type is unsupported	Use a supported native Spanner array type. ARRAY<STRUCT> and PostgreSQL json[] are not supported.
An ARRAY or JSON column is used as KeyColumns	Remove the column from KeyColumns. The application halts when it receives the first DML event for that table.
Expected ARRAY column is not recognized after a schema change	Apply the native target DDL manually, then restart the application so the writer refreshes the target schema.
JSON element is rejected by Spanner	Check that the JSON value is syntactically valid. The writer does not validate JSON syntax in an ARRAY<JSON> or jsonb[] element before sending it to Spanner.

Schema and DDL handling

The writer does not create or modify native ARRAY columns through DDL replication. Pre-create native ARRAY columns manually.

Incoming DDL and source	Behavior
CREATE TABLE or ADD COLUMN with an array column from BigQuery, Snowflake, Databricks, or PostgreSQL	The writer creates VARCHAR instead of a native array column: STRING(MAX) in GoogleSQL and character varying in PostgreSQL. That fallback column cannot receive later native-array writes.
CREATE TABLE or ADD COLUMN with an array column from another source	The application halts.
DROP COLUMN or DROP TABLE	The operation proceeds normally.

To add, remove, or change an array column while the application is running:

Set CDDL Action to Halt on the writer, or set CDDL Action to Quiesce on the source so that the source processes preceding DML events before it stops.
Manually apply the required ARRAY DDL to the Spanner target table.
Restart the application so the writer fetches the target schema and recognizes the changed column.

Interaction with writer features

No additional target-adapter property is required. The writer detects supported target ARRAY columns automatically.
Do not use an ARRAY or JSON column as KeyColumns. The application halts when it receives the first DML event for that table.
Execute Insert As Update and Execute Update As Insert have no additional restrictions for ARRAY columns.
When Preserve Source Transaction Boundary is enabled, ARRAY columns are replicated as part of the target transaction in the same way as other columns.
Array-containing rows are included in the existing INSERTS, UPDATES, DELETES, and PKUPDATES metrics. No separate ARRAY metric is added.

Limitations

ARRAY and JSON/JSONB columns cannot be key columns.
The writer supports only the target types and aliases listed in Supported array types.
Cloud Spanner type, row-size, mutation, and transaction limits apply to ARRAY writes.

Examples

Example 1: All supported GoogleSQL array types

This example uses SpannerBatchReader and SpannerWriter to copy all the supported GoogleSQL array types. SpannerBatchReader delivers array column values as string notation; Spanner Writer converts the values to the respective target types.

The second INSERT illustrates null elements, empty arrays, a null array column, a quoted null string, and a timestamp with an embedded space.

Replace every angle-bracketed placeholder with your environment values. Create the target table before deploying the application.

-- Source table (Spanner GoogleSQL)
CREATE TABLE AllArrayTypes (
  Id             INT64 NOT NULL,
  BoolArray      ARRAY<BOOL>,
  IntArray       ARRAY<INT64>,
  Float32Array   ARRAY<FLOAT32>,
  Float64Array   ARRAY<FLOAT64>,
  StringArray    ARRAY<STRING(MAX)>,
  BytesArray     ARRAY<BYTES(MAX)>,
  DateArray      ARRAY<DATE>,
  TimestampArray ARRAY<TIMESTAMP>,
  NumericArray   ARRAY<NUMERIC>,
  JsonArray      ARRAY<JSON>
) PRIMARY KEY (Id);

-- Target table (Spanner GoogleSQL)
CREATE TABLE AllArrayTypesTarget (
  Id             INT64 NOT NULL,
  BoolArray      ARRAY<BOOL>,
  IntArray       ARRAY<INT64>,
  Float32Array   ARRAY<FLOAT32>,
  Float64Array   ARRAY<FLOAT64>,
  StringArray    ARRAY<STRING(MAX)>,
  BytesArray     ARRAY<BYTES(MAX)>,
  DateArray      ARRAY<DATE>,
  TimestampArray ARRAY<TIMESTAMP>,
  NumericArray   ARRAY<NUMERIC>,
  JsonArray      ARRAY<JSON>
) PRIMARY KEY (Id);

-- TQL
CREATE OR REPLACE APPLICATION AllTypesArrayCopy;

CREATE OR REPLACE SOURCE ArraySource USING Global.SpannerBatchReader (
  ConnectionURL:   'jdbc:cloudspanner:/projects/<source-project>/instances/<source-instance>/databases/<source-database>;credentials=<source-service-account-key-path>',
  Tables:          'AllArrayTypes',
  CheckColumn:     'AllArrayTypes=Id',
  FetchSize:       100,
  pollingInterval: '10sec'
) OUTPUT TO ArraySourceStream;

CREATE OR REPLACE TARGET ArrayTarget USING Global.SpannerWriter (
  ProjectId:         '<target-project>',
  InstanceID:        '<target-instance>',
  ServiceAccountKey: '<target-service-account-key-path>',
  CheckpointTable:   'CHKPOINT',
  Tables:            'AllArrayTypes,<target-database>.AllArrayTypesTarget'
) INPUT FROM ArraySourceStream;

END APPLICATION AllTypesArrayCopy;

-- Insert
INSERT INTO AllArrayTypes (Id, BoolArray, IntArray, Float32Array, Float64Array,
  StringArray, BytesArray, DateArray, TimestampArray, NumericArray, JsonArray)
VALUES (
  1,
  [TRUE, FALSE],
  [10, 20, 30],
  ARRAY<FLOAT32>[1.1, 2.2],
  [3.14, 6.28],
  ['hello', 'world'],
  [b'abc', b'def'],
  [DATE '2024-01-01', DATE '2024-02-01'],
  [TIMESTAMP '2024-01-01T10:00:00Z', TIMESTAMP '2024-01-02T10:00:00Z'],
  ARRAY<NUMERIC>[123.45, 678.90],
  [JSON '{"a":1}', JSON '{"b":2}']
);

-- Result in AllArrayTypesTarget
Id:            1
BoolArray:     [true, false]
IntArray:      [10, 20, 30]
Float32Array:  [1.1, 2.2]
Float64Array:  [3.14, 6.28]
StringArray:   ["hello", "world"]
BytesArray:    [<bytes:abc>, <bytes:def>]
DateArray:     [2024-01-01, 2024-02-01]
TimestampArray:[2024-01-01T10:00:00Z, 2024-01-02T10:00:00Z]
NumericArray:  [123.45, 678.90]
JsonArray:     [{"a":1}, {"b":2}]

-- Null element, empty array, null column, quoted null string, and timestamp with embedded space
INSERT INTO AllArrayTypes (Id, BoolArray, IntArray, StringArray, DateArray, TimestampArray)
VALUES (
  2,
  [TRUE, NULL, FALSE],
  [],
  ['hello, world', 'null', NULL],
  NULL,
  [TIMESTAMP '2024-01-01 10:00:00+00:00']
);

-- Result in AllArrayTypesTarget
Id:            2
BoolArray:     [true, null, false]
IntArray:      []
               -- empty array stored as zero elements, not null
StringArray:   ["hello, world", "null", null]
               -- comma inside a quoted element is literal content, not a delimiter
               -- "null" in quotes is the four-character string, not a null element
               -- unquoted NULL is a null element
DateArray:     null
               -- a null source-column value writes a null array, not []
TimestampArray:[2024-01-01T10:00:00Z]
               -- a timestamp with an embedded space is quoted in string notation: {"2024-01-01 10:00:00+00:00"}

Example 2: Replicate PostgreSQL-dialect arrays via CDC

This example uses PostgreSQL Reader and Spanner PG Dialect Writer to replicate array columns via CDC. It shows a text column that carries string notation written as int8[], along with bigint[], double precision[], character varying[], and jsonb[] arrays.

-- Source table (PostgreSQL)
CREATE TABLE public.inventory (
  id        bigint PRIMARY KEY,
  quantities bigint[],
  tag_ids   text,               -- string notation '{...}' written as int8[] on the target
  labels    text[],
  prices    double precision[],
  payloads  jsonb[]
);

-- Target table (Spanner PostgreSQL dialect)
CREATE TABLE public.inventory (
  id        bigint PRIMARY KEY,
  quantities bigint[],
  tag_ids   int8[],
  labels    character varying[],
  prices    double precision[],
  payloads  jsonb[]
);

-- TQL
CREATE OR REPLACE APPLICATION PGArrayCDC RECOVERY 30 SECOND INTERVAL USE EXCEPTIONSTORE TTL : '7d' ;

CREATE OR REPLACE SOURCE PGSource USING Global.PostgreSQLReader (
  ConnectionURL: 'jdbc:postgresql://<host>:5432/<database>',
  Username:      '<username>',
  Password:      '<password>',
  Tables:        'public.inventory'
) OUTPUT TO PGStream;

CREATE OR REPLACE TARGET SpannerPGTarget USING Global.SpannerPGDialectWriter (
  ProjectId:         '<project>',
  InstanceID:        '<instance>',
  DatabaseName:      '<database>',
  ServiceAccountKey: '<key-path>',
  CheckpointTable:   'public.chkpoint',
  Tables:            'inventory,public.inventory'
) INPUT FROM PGStream;

END APPLICATION PGArrayCDC;

-- Insert
INSERT INTO public.inventory (id, quantities, tag_ids, labels, prices, payloads)
VALUES (
  1,
  '{10,20,30}',
  '{101,102}',
  '{"apples","oranges"}',
  '{9.99,12.50}',
  ARRAY['{"sku":"X1"}'::jsonb, '{"sku":"X2"}'::jsonb]
);

-- Result in public.inventory (Spanner)
id:        1
quantities:[10, 20, 30]
tag_ids:   [101, 102]      -- string '{101,102}' parsed and written as int8[]
labels:    ["apples", "oranges"]
prices:    [9.99, 12.50]
payloads:  [{"sku":"X1"}, {"sku":"X2"}]

Example 3: Replicate Oracle VARRAY columns via CDC

This example replicates Oracle VARRAY columns to a Spanner GoogleSQL-dialect target using Oracle Reader and Spanner Writer.

-- Source DDL (Oracle)
-- VARRAY type definitions
CREATE OR REPLACE TYPE int_array_t       AS VARRAY(10) OF NUMBER;
CREATE OR REPLACE TYPE float_array_t     AS VARRAY(10) OF BINARY_FLOAT;
CREATE OR REPLACE TYPE string_array_t    AS VARRAY(10) OF VARCHAR2(100);
CREATE OR REPLACE TYPE date_array_t      AS VARRAY(10) OF DATE;
CREATE OR REPLACE TYPE timestamp_array_t AS VARRAY(10) OF TIMESTAMP;

CREATE TABLE QATEST.ARRAY_TYPES (
  id              NUMBER         PRIMARY KEY,
  int_array       int_array_t,
  float_array     float_array_t,
  string_array    string_array_t,
  date_array      date_array_t,
  timestamp_array timestamp_array_t
);

-- Target table (Spanner GoogleSQL dialect)
CREATE TABLE array_types (
  id              INT64 NOT NULL,
  int_array       ARRAY<INT64>,
  float_array     ARRAY<FLOAT32>,
  string_array    ARRAY<STRING(MAX)>,
  date_array      ARRAY<DATE>,
  timestamp_array ARRAY<TIMESTAMP>
) PRIMARY KEY (id);

-- TQL
CREATE OR REPLACE APPLICATION OracleArrayCDC;

CREATE OR REPLACE SOURCE OracleSource USING Global.OracleReader (
  ConnectionURL:         'jdbc:oracle:thin:@//<host>:1521/ORCL',
  Username:              '<username>',
  Password:              '<password>',
  Tables:                '"QATEST"."ARRAY_TYPES"',
  DictionaryMode:        'OnlineCatalog',
  CommittedTransactions: true,
  SendBeforeImage:       true
) OUTPUT TO OracleStream;

CREATE OR REPLACE TARGET SpannerPGTarget USING Global.SpannerPGDialectWriter (
  ProjectId:                         '<project>',
  InstanceID:                        '<instance>',
  DatabaseName:                      '<database>',
  ServiceAccountKey:                 '<key-path>',
  CheckpointTable:                   'public.chkpoint',
  Tables:                            '"QATEST"."ARRAY_TYPES",<database>.public.array_types'
) INPUT FROM OracleStream;

END APPLICATION OracleArrayCDC;

-- Insert
INSERT INTO QATEST.ALL_ARRAY_TYPES (id, int_array, float_array, string_array, date_array, timestamp_array)
VALUES (
  1,
  int_array_t(10, 20, 30),
  float_array_t(1.1, 2.2),
  string_array_t('urgent', 'fragile'),
  date_array_t(DATE '2024-01-01', DATE '2024-02-01'),
  timestamp_array_t(TIMESTAMP '2024-01-01 10:00:00', TIMESTAMP '2024-01-02 10:00:00')
);

-- Result in public.all_array_types (Spanner)
id:              1
int_array:       [10, 20, 30]
float_array:     [1.1, 2.2]
string_array:    ["urgent", "fragile"]
date_array:      [2024-01-01, 2024-02-01]
timestamp_array: [2024-01-01T10:00:00Z, 2024-01-02T10:00:00Z]