This page describes syntax and behavior for performing essential arraymanagement tasks for the PostgreSQL interface for Spanner.Arrays for the PostgreSQL interface share the same syntax asarrays inopen source PostgreSQL,except as described inArray type limitations.One important limitation is no support for multidimensional arrays.

Declaration of arrays

The following is an example of how to create a table that declares arrays:

CREATETABLElawn_care_business(client_nametextPRIMARYKEY,quarterly_feeinteger[],services_renderedtext[]);

You name an array by adding square brackets ([]) to the data type name of thearray elements. The previous statement creates a table namedlawn_care_businesswith two one-dimensional arrays. The first array,quarterly_fee, is aninteger array. The second array,services_rendered, is atext array.

You can also specify the size of arrays when creating them:

CREATETABLElawn_care_business(client_nametextPRIMARYKEY,quarterly_feeinteger[4],services_renderedtext[3]);

Note, however, that array size is not enforced. You can create an array with aspecified size, but the size can be changed after the initial declaration.

Array keyword constructor syntax

The PostgreSQL interface also supports the ARRAY keywordconstructor syntax, which lets you include expressions, add columns, andmore.

Thenative PostgreSQL array constructor syntax documentation provides detailson using the syntax. The PostgreSQL interface supports thisfunctionality, with the exception of multi-dimensional arrays.

The following command creates a simple table using the ARRAY syntax:

CREATETABLEstudent_id_numbers(idintegerPRIMARYKEY,student_phone_numbersintegerARRAY[]);

Input values into array columns

A PostgreSQL interface array can only store values of onePostgreSQL type. For a list of supportedPostgreSQL interface data types, seePostgreSQL data types.Nested arrays are not supported.

The standard array format for inputting values into arrays for PostgreSQL lookslike this:

When inputting values using this format you should be aware of thefollowing caveats:

• You can put double quotes around any value, even integers.
• You must put double quotes around a string if it contains a comma or curlybrace.
• To enter aNULL value, enter eithernull orNULL. If you want a stringthat merely says NULL, enter "NULL".

You can also use theARRAY constructor syntax to input values into an array:

Access array values

You can run queries on arrays in a table. Continuing the previous example, thefollowing query returns the names of clients who were charged a different feebetween the first and second quarters of the year:

SELECTclient_nameFROMlawn_care_businessWHEREquarterly_fee[1] <>quarterly_fee[2];

Result:

 client_name------------- Esmae

PostgreSQL arrays are 1-based, meaning that for an array of sizen,the first element isarray[1] and the last element is atarray[n].

The following query gets the third quarter fee for all clients:

SELECTquarterly_fee[3]FROMlawn_care_business;

Result:

 quarterly_fee--------------- 1000 2500

Modify array values

To modify the values of an array, you must provide the values for each elementin the array. For example:

UPDATElawn_care_businessSETquarterly_fee='{2500,2500,2800,2800}'WHEREclient_name='Esmae';

The following example updates the same information usingARRAY expressionsyntax:

UPDATElawn_care_businessSETquarterly_fee=ARRAY[2500,2500,2800,2800]WHEREclient_name='Esmae';

You cannot currently update specific values of an array. This includesappending elements to an array at an unused index. For example,the following command is not supported:

UPDATElawn_care_businessSETservices_rendered[4]='reseeding'WHEREclient_name='Bagdan';

Instead, if you wish to add, remove, or the modify contents of an array, includethe entire array in the query:

UPDATElawn_care_businessSETservices_rendered='{"mowing", "fertilizing", "weeding", "reseeding"}'WHEREclient_name='Bagdan';

Search for values in arrays

Each value must be checked when searching for a value in an array. If you knowthe size of the array, you can do this manually:

SELECT*FROMlawn_care_businessWHEREquarterly_fee[1]=1000ORquarterly_fee[2]=1000ORquarterly_fee[3]=1000ORquarterly_fee[4]=1000;

SELECTsome_numbers,array_length(some_numbers,1)ASlenFROM(SELECTARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECTARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECTARRAY[5,10]ASsome_numbers)ASsequences;/*--------------------+--------* | some_numbers       | len    | +--------------------+--------+ | [0, 1, 1, 2, 3, 5] | 6      | | [2, 4, 8, 16, 32]  | 5      | | [5, 10]            | 2      | *--------------------+--------*/

To convert anARRAY into a set of rows, also known as flattening, use theUNNEST operator.UNNEST takes anARRAY and returnsa table with a single row for each element in theARRAY.

BecauseUNNEST rearranges the order of theARRAY elements, you might want torestore order to the table. To do so, use the optionalWITH ORDINALITY clauseto return an additional column with the index for each array element, then usetheORDER BY clause to order the rows by their offset.

Example

SELECT*FROMUNNEST(ARRAY['foo','bar','baz','qux','corge','garply','waldo','fred'])WITHORDINALITYASmy_table(element,ordinality)ORDERBYordinality;/-----------------------*|element|ordinality|+----------+------------+|foo|1||bar|2||baz|3||qux|4||corge|5||garply|6||waldo|7||fred|8|----------/----------- */

Creating arrays from subqueries

You can convert a subquery result into an array using theARRAY() function.

Example

SELECTsome_numbers,ARRAY(SELECTx*2FROMUNNEST(some_numbers)ASX)ASdoubledFROM(SELECTARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECTARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECTARRAY[5,10]ASsome_numbers)ASsequences;/*--------------------+---------------------* | some_numbers       | doubled             | +--------------------+---------------------+ | [0, 1, 1, 2, 3, 5] | [0, 2, 2, 4, 6, 10] | | [2, 4, 8, 16, 32]  | [4, 8, 16, 32, 64]  | | [5, 10]            | [10, 20]            | *--------------------+---------------------*/

The suquery called sequences in the example contains a column,some_numbers, oftypebigint[]. The query contains another subquery that selects each row in thesome_numbers column and usesUNNEST to return the arrayas a set of rows. Then, it multiplies each value by two, and re-combinesthe rows into an array using theARRAY() operator.

Filtering arrays

The following examples use subqueries and theWHERE clause to filter an arrayin the query.

SELECTARRAY(SELECTx*2FROMUNNEST(some_numbers)ASxWHEREx <5)ASdoubled_less_than_fiveFROM(SELECTARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECTARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECTARRAY[5,10]ASsome_numbers)sequences;/*------------------------* | doubled_less_than_five | +------------------------+ | [0, 2, 2, 4, 6]        | | [4, 8]                 | | []                     | *------------------------*/

Notice that the third row contains an empty array, because the elements in thecorresponding original row ([5, 10]) did not meet the filter requirement ofx < 5.

You can also filter arrays by usingSELECT DISTINCT to return onlyunique elements within an array.

SELECTARRAY(SELECTDISTINCTxFROMUNNEST(some_numbers)ASx)ASunique_numbersFROM(SELECTARRAY[0,1,1,2,3,5]ASsome_numbers)ASsequences;/*-----------------* | unique_numbers  | +-----------------+ | [0, 1, 2, 3, 5] | *-----------------*/

Scanning arrays

To check if an array contains a specific value, use theANY/SOME clause. Tocheck if an array contains a value matching a condition, use either theALLclause orEXISTS operator withUNNEST.

Scanning for specific values

To scan an array for a specific value, use theANY/SOME clause.

The following example returnstrue if the array contains the number2.

SELECT2=ANY(ARRAY[0,1,1,2,3,5])AScontains_value;/*----------------* | contains_value | +----------------+ | true           | *----------------*/

To return the rows of a table where the array column contains a specific value,filter the results ofANY/SOME using theWHERE clause.

Example

The following example returns theid value for the rows where the array columncontains the value2.

SELECTidASmatching_rowsFROM(SELECT1ASid,ARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT2ASid,ARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECT3ASid,ARRAY[5,10]ASsome_numbers)ASsequencesWHERE2=ANY(sequences.some_numbers)ORDERBYmatching_rows;/*---------------* | matching_rows | +---------------+ | 1             | | 2             | *---------------*/

Scanning for values that satisfy a condition

To scan an array for values that match a condition, useUNNEST with subqueriesto return a table of the elements in the array, useWHERE to filter theresulting table in the subquery, and useEXISTS to check if the filteredtable contains any rows.

Example

The following example returns theid value for rows where the array containsvalues greater than5.

SELECTidASmatching_rowsFROM(SELECT1ASid,ARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT2ASid,ARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECT3ASid,ARRAY[5,10]ASsome_numbers)ASsequencesWHEREEXISTS(SELECT*FROMUNNEST(some_numbers)ASxWHEREx >5);/*---------------* | matching_rows | +---------------+ | 2             | | 3             | *---------------*/

Arrays and aggregation

You can aggregate values into an array usingARRAY_AGG().

SELECTARRAY_AGG(fruit)ASfruit_basketFROM(SELECT'apple'ASfruitUNIONALLSELECT'pear'ASfruitUNIONALLSELECT'banana'ASfruit)ASfruits;/*-----------------------* | fruit_basket          | +-----------------------+ | [apple, pear, banana] | *-----------------------*/

The array returned byARRAY_AGG() is in an arbitrary order, since the order inwhich the function concatenates values is not guaranteed.

You can also apply aggregate functions such asSUM() to the elements in anarray. For example, the following query returns the sum of elements foreach row of the subquery result.

SELECTsome_numbers,(SELECTSUM(x)FROMUNNEST(s.some_numbers)ASx)ASsumsFROM(SELECTARRAY[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECTARRAY[2,4,8,16,32]ASsome_numbersUNIONALLSELECTARRAY[5,10]ASsome_numbers)ASs;/*--------------------+------* | some_numbers       | sums | +--------------------+------+ | [0, 1, 1, 2, 3, 5] | 12   | | [2, 4, 8, 16, 32]  | 62   | | [5, 10]            | 15   | *--------------------+------*/

Converting arrays to strings

Thearray_to_string() function lets you convert a text array to a singletext value where the resulting value is the ordered concatenation of the arrayelements.

The second argument is the separator that the function inserts betweeninputs to produce the output; this second argument must use the same type asthe elements of the first argument.

Example

SELECTARRAY_TO_STRING(greeting,' ')ASgreetingsFROM(SELECTARRAY['Hello','World']ASgreeting)ASwords;/*-------------* | greetings   | +-------------+ | Hello World | *-------------*/

The optional third argument takes the place ofNULL values in the inputarray.

• If you omit this argument, then the function ignoresNULL array elements.
• If you provide an empty string, the function inserts the separator specifiedin the second argument forNULL array elements.

Example

SELECTARRAY_TO_STRING(arr,'.','N')ASnon_empty_string,ARRAY_TO_STRING(arr,'.','')ASempty_string,ARRAY_TO_STRING(arr,'.')ASomittedFROM(SELECTARRAY['a',NULL,'b',NULL,'c',NULL]ASarr)ASsubquery;/*------------------+--------------+---------* | non_empty_string | empty_string | omitted | +------------------+--------------+---------+ | a.N.b.N.c.N      | a..b..c.     | a.b.c   | *------------------+--------------+---------*/

Combining arrays

In some cases, you might want to combine multiple arrays into a single array.You can accomplish this using the|| operator.

SELECTARRAY[1,2]||ARRAY[3,4]||ARRAY[5,6]AScount_to_six;/*--------------------------------------------------* | count_to_six                                     | +--------------------------------------------------+ | [1, 2, 3, 4, 5, 6]                               | *--------------------------------------------------*/