Categories:

Semi-structured and structured data functions (Array/Object)

ARRAY_APPEND¶

Returns an array containing all elements from the source array as well as the new element. The new element is located at the end of the array.

See also:

ARRAY_INSERT , ARRAY_PREPEND

Syntax¶

ARRAY_APPEND( <array> , <new_element> )
Copy

Arguments¶

array

The source array.

new_element

The element to be appended. The type of the element depends on the type of the array:

  • If array is a semi-structured array, the element can be of almost any data type. The data type can be different from the data type(s) of the existing elements in the array.

  • If array is a structured array, the type of the new element must be coercible to the type of the array.

Returns¶

The data type of the returned value is ARRAY.

When you pass a structured array to the function, the function returns a structured array of the same type.

If the source array is NULL, the function returns NULL.

Examples¶

The examples use the following table with an ARRAY column:

CREATE OR REPLACE TABLE array_append_examples (array_column ARRAY);

INSERT INTO array_append_examples (array_column)
  SELECT ARRAY_CONSTRUCT(1, 2, 3);

SELECT * FROM array_append_examples;
Copy
+--------------+
| ARRAY_COLUMN |
|--------------|
| [            |
|   1,         |
|   2,         |
|   3          |
| ]            |
+--------------+

Add an element of the same type to the array:

UPDATE array_append_examples
  SET array_column = ARRAY_APPEND(array_column, 4);
Copy

Query the table to see the new element added to the array:

SELECT * FROM array_append_examples;
Copy
+--------------+
| ARRAY_COLUMN |
|--------------|
| [            |
|   1,         |
|   2,         |
|   3,         |
|   4          |
| ]            |
+--------------+

Add an element of a different type to the array:

UPDATE array_append_examples
  SET array_column = ARRAY_APPEND(array_column, 'five');
Copy

Query the table to see the new element added to the array and the data type of each element in the array:

SELECT array_column,
       ARRAY_CONSTRUCT(
        TYPEOF(array_column[0]),
        TYPEOF(array_column[1]),
        TYPEOF(array_column[2]),
        TYPEOF(array_column[3]),
        TYPEOF(array_column[4])) AS type
  FROM array_append_examples;
Copy
+--------------+--------------+
| ARRAY_COLUMN | TYPE         |
|--------------+--------------|
| [            | [            |
|   1,         |   "INTEGER", |
|   2,         |   "INTEGER", |
|   3,         |   "INTEGER", |
|   4,         |   "INTEGER", |
|   "five"     |   "VARCHAR"  |
| ]            | ]            |
+--------------+--------------+
Language: English