ARRAY Type

Apache Phoenix 3.0/4.0 release introduces support for the JDBC ARRAY type. Any primitive type may be used in an ARRAY. Here is an example of declaring an array type when creating a table:

CREATE TABLE regions (
    region_name VARCHAR PRIMARY KEY,
    zips VARCHAR ARRAY[10],
    CONSTRAINT pk PRIMARY KEY (region_name));

or alternately:

CREATE TABLE regions (
    region_name VARCHAR PRIMARY KEY,
    zips VARCHAR[10],
    CONSTRAINT pk PRIMARY KEY (region_name));

Insertion into the array may be done entirely through a SQL statement:

UPSERT INTO regions(region_name,zips) VALUES('SF Bay Area',ARRAY['94115','94030','94125']);

or programmatically through JDBC:

PreparedStatement stmt = conn.prepareStatement("UPSERT INTO regions(region_name,zips) VALUES(?,?)");
stmt.setString(1,"SF Bay Area");
String[] zips =  new String[] {"94115","94030","94125"};
Array array = conn.createArrayOf("VARCHAR", zips);
stmt.setArray(2, array);
stmt.execute();

The entire element may be selected:

SELECT zips FROM regions WHERE region_name = 'SF Bay Area';

Or an individual element in the array may be accessed via a one-based subscript notation:

SELECT zip[1] FROM regions WHERE region_name = 'SF Bay Area';

The length of the array grows dynamically as needed with the current length and is accessed through the ARRAY_LENGTH build it function:

SELECT ARRAY_LENGTH(zips) FROM regions;

Attempt to access an array element beyond the current length will evaluate to null.

##Limitations

• Only one dimensional arrays are currently supported
• For an array of fixed width types, null elements occurring in the middle of an array are not tracked.
• The declaration of an array length at DDL time has no effect.
• An array may only be used as the last column in a primary key constraint.
• Partial update of an array is not currently possible. Instead, the array may be manipulated on the client-side and then upserted back in its entirety.