Structured data types¶

Specifying a structured ARRAY type¶

To specify a structured ARRAY type, use the following syntax:

ARRAY( <element_type> [ NOT NULL ] )

Where:

• element_type is the Snowflake data type of the elements in this ARRAY.

  You can also specify a structured ARRAY, a structured OBJECT, or a MAP as the type of the element.

  Note

  In the definition of an Iceberg table column, you can’t specify a VARIANT, semi-structured ARRAY, or semi-structured OBJECT as the type of the ARRAY element.

• NOT NULL specifies that the ARRAY can’t contain any elements that are NULL.

For example, compare the types returned by the SYSTEM$TYPEOF function in the following statement:

• The first column expression casts a semi-structured ARRAY value to a structured ARRAY value (an ARRAY of NUMBER elements).

• The second column expression specifies a semi-structured ARRAY value.

SELECT
  SYSTEM$TYPEOF(
    [1, 2, 3]::ARRAY(NUMBER)
  ) AS structured_array,
  SYSTEM$TYPEOF(
    [1, 2, 3]
  ) AS semi_structured_array;

+-------------------------------+-----------------------+
| STRUCTURED_ARRAY              | SEMI_STRUCTURED_ARRAY |
|-------------------------------+-----------------------|
| ARRAY(NUMBER(38,0))[LOB]      | ARRAY[LOB]            |
+-------------------------------+-----------------------+

Specifying a structured OBJECT type¶

To specify a structured OBJECT type, use the following syntax:

OBJECT(
  [
    <key> <value_type> [ NOT NULL ]
    [ , <key> <value_type> [ NOT NULL ] ]
    [ , ... ]
  ]
)

Where:

• key specifies a key for the OBJECT type.

  • Each key in an OBJECT definition must be unique.

  • The order of the keys is part of the OBJECT definition. Comparing two OBJECT values that have the same keys in a different order isn’t allowed. (A compile time error occurs.)

  • If you don’t specify any key but specify the parentheses (that is, if you use OBJECT()), the resulting type is a structured OBJECT that contains no keys. A structured OBJECT with no keys is different from a semi-structured OBJECT.

• value_type is the Snowflake data type of the value corresponding to the key.

  You can also specify a structured ARRAY, a structured OBJECT, or a MAP as the type of the value.

  Note

  In the definition of an Iceberg table column, you can’t specify a VARIANT, semi-structured ARRAY, or semi-structured OBJECT as the type of the value corresponding to the OBJECT key.

• NOT NULL specifies that the value corresponding to the key can’t be NULL.

For example, compare the types returned by the SYSTEM$TYPEOF function in the following statement:

• The first column expression casts a semi-structured OBJECT value to a structured OBJECT value that contains the following keys and values:

  • A key named str with a VARCHAR value that is not NULL.

  • A key named num with a NUMBER value.

• The second column expression specifies a semi-structured OBJECT value.

SELECT
  SYSTEM$TYPEOF(
    {
      'str': 'test',
      'num': 1
    }::OBJECT(
      str VARCHAR NOT NULL,
      num NUMBER
    )
  ) AS structured_object,
  SYSTEM$TYPEOF(
    {
      'str': 'test',
      'num': 1
    }
  ) AS semi_structured_object;

+---------------------------------------------------------------+------------------------+
| STRUCTURED_OBJECT                                             | SEMI_STRUCTURED_OBJECT |
|---------------------------------------------------------------+------------------------|
| OBJECT(str VARCHAR(16777216) NOT NULL, num NUMBER(38,0))[LOB] | OBJECT[LOB]            |
+---------------------------------------------------------------+------------------------+

Specifying a MAP type¶

To specify a MAP type, use the following syntax:

MAP( <key_type> , <value_type> [ NOT NULL ] )

Where:

• key_type is the Snowflake data type of the key for the map. You must use one of the following types for keys:

  • VARCHAR

  • NUMBER with the scale 0

  You can’t use a floating point data type as the type for the key.

  Map keys can’t be NULL.

• value_type is the Snowflake data type of the values in the map.

  You can also specify a structured ARRAY, a structured OBJECT, or a MAP as the type of the values.

  Note

  In the definition of an Iceberg table column, you can’t specify a VARIANT, semi-structured ARRAY, or semi-structured OBJECT as the type of the value in the MAP.

• NOT NULL specifies that the value corresponding to the key can’t be NULL.

The following example casts a semi-structured OBJECT value to a MAP value and uses the SYSTEM$TYPEOF function to print the resulting type of the value. The MAP associates VARCHAR keys with VARCHAR values.

SELECT
  SYSTEM$TYPEOF(
    {
      'a_key': 'a_val',
      'b_key': 'b_val'
    }::MAP(VARCHAR, VARCHAR)
  ) AS map_example;

+------------------------------------------------+
| MAP_EXAMPLE                                    |
|------------------------------------------------|
| MAP(VARCHAR(16777216), VARCHAR(16777216))[LOB] |
+------------------------------------------------+

Explicitly casting a semi-structured type to a structured type¶

To explicitly cast a value of a semi-structured type to a value of a structured type, you can call the CAST function or use the :: operator.

You can only cast values of the following semi-structured types to values of the corresponding structured type; otherwise, a runtime error occurs.

The next sections provide more detail about how the types are cast:

• Casting semi-structured ARRAY and VARIANT values to structured ARRAY values

• Casting semi-structured OBJECT and VARIANT values to structured OBJECT values

• Casting semi-structured OBJECT and VARIANT values to MAP values

SELECT
  SYSTEM$TYPEOF(
    CAST ([1,2,3] AS ARRAY(NUMBER))
  ) AS array_cast_type,
  SYSTEM$TYPEOF(
    CAST ([1,2,3]::VARIANT AS ARRAY(NUMBER))
  ) AS variant_cast_type;

SELECT
  SYSTEM$TYPEOF(
    [1,2,3]::ARRAY(NUMBER)
  ) AS array_cast_type,
  SYSTEM$TYPEOF(
    [1,2,3]::VARIANT::ARRAY(NUMBER)
  ) AS variant_cast_type;

+--------------------------+--------------------------+
| ARRAY_CAST_TYPE          | VARIANT_CAST_TYPE        |
|--------------------------+--------------------------|
| ARRAY(NUMBER(38,0))[LOB] | ARRAY(NUMBER(38,0))[LOB] |
+--------------------------+--------------------------+

SELECT
  SYSTEM$TYPEOF(
    CAST ({'city':'San Mateo','state':'CA'} AS OBJECT(city VARCHAR, state VARCHAR))
  ) AS object_cast_type,
  SYSTEM$TYPEOF(
    CAST ({'city':'San Mateo','state':'CA'}::VARIANT AS OBJECT(city VARCHAR, state VARCHAR))
  ) AS variant_cast_type;

SELECT
  SYSTEM$TYPEOF(
     {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  ) AS object_cast_type,
  SYSTEM$TYPEOF(
     {'city':'San Mateo','state':'CA'}::VARIANT::OBJECT(city VARCHAR, state VARCHAR)
  ) AS variant_cast_type;

+--------------------------------------------------------------+--------------------------------------------------------------+
| OBJECT_CAST_TYPE                                             | VARIANT_CAST_TYPE                                            |
|--------------------------------------------------------------+--------------------------------------------------------------|
| OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] |
+--------------------------------------------------------------+--------------------------------------------------------------+

SELECT
  SYSTEM$TYPEOF(
    CAST ({'my_key':'my_value'} AS MAP(VARCHAR, VARCHAR))
  ) AS map_cast_type,
  SYSTEM$TYPEOF(
    CAST ({'my_key':'my_value'} AS MAP(VARCHAR, VARCHAR))
  ) AS variant_cast_type;

SELECT
  SYSTEM$TYPEOF(
    {'my_key':'my_value'}::MAP(VARCHAR, VARCHAR)
  ) AS map_cast_type,
  SYSTEM$TYPEOF(
    {'my_key':'my_value'}::VARIANT::MAP(VARCHAR, VARCHAR)
  ) AS variant_cast_type;

+------------------------------------------------+------------------------------------------------+
| MAP_CAST_TYPE                                  | VARIANT_CAST_TYPE                              |
|------------------------------------------------+------------------------------------------------|
| MAP(VARCHAR(16777216), VARCHAR(16777216))[LOB] | MAP(VARCHAR(16777216), VARCHAR(16777216))[LOB] |
+------------------------------------------------+------------------------------------------------+

Explicitly casting a structured type to a semi-structured type¶

To explicitly cast a value of a structured type to a value of a semi-structured type, you can call the CAST function, use the :: operator, or call one of the conversion functions (for example, TO_ARRAY, TO_OBJECT, or TO_VARIANT).

For example:

• If col_structured_array is ARRAY(VARCHAR) type:

  • CAST(col_structured_array AS ARRAY) returns a semi-structured ARRAY value.

  • CAST(col_structured_array AS VARIANT) returns a VARIANT value that holds a semi-structured ARRAY value.

• If col_structured_object is OBJECT(name VARCHAR, state VARCHAR) type:

  • CAST(col_structured_object AS OBJECT) returns a semi-structured OBJECT value.

  • CAST(col_structured_object AS VARIANT) returns a VARIANT value that holds a semi-structured OBJECT value.

• If col_map is MAP(VARCHAR, VARCHAR) type:

  • CAST(col_map AS OBJECT) returns a semi-structured OBJECT value.

  • CAST(col_map AS VARIANT) returns a VARIANT value that holds a semi-structured OBJECT value.

Note the following:

• When you are casting to a semi-structured OBJECT value, the order of keys in the structured OBJECT value isn’t preserved.

• When you are casting a structured OBJECT or MAP value to a semi-structured OBJECT or VARIANT value, any NULL values are converted to JSON null values.

  If you are casting a structured ARRAY value to a VARIANT value, NULL values are preserved as is.

  SELECT [1,2,NULL,3]::ARRAY(INTEGER)::VARIANT;
  

  Copy

  +---------------------------------------+
  | [1,2,NULL,3]::ARRAY(INTEGER)::VARIANT |
  |---------------------------------------|
  | [                                     |
  |   1,                                  |
  |   2,                                  |
  |   undefined,                          |
  |   3                                   |
  | ]                                     |
  +---------------------------------------+
  

• If you are casting a MAP value that uses a NUMBER type for keys, the MAP keys are converted to strings in the returned OBJECT value.

Implicit casting a value (coercion)¶

The following rules apply to implicitly casting (coercion) from a value of one structured type to a value of another structured type:

• A structured type value can be coerced to another structured type value if the two basic types are the same:

  • An ARRAY value of one type can be coerced to an ARRAY value of another type, provided that the first element type is coercible to the second element type.

    An element type can be coerced to another element type in either of the following cases:

    • Both types are numeric. The following cases are supported:

      • Both use the same numeric type but possibly differ in precision and/or scale.

      • Coercing NUMBER to FLOAT (and vice versa).

    • Both types are timestamps. The following cases are supported:

      • Both use the same type but possibly differ in precision.

      • Coercing TIMESTAMP_LTZ to TIMESTAMP_TZ (and vice versa).

    For example:

    • An ARRAY(NUMBER) value can be coerced to an ARRAY(DOUBLE) value.

    • An ARRAY(DATE) value can’t be coerced to an ARRAY(NUMBER) value.

  • An OBJECT value with one type definition can be coerced to an OBJECT value of with another type definition only if all of the following are true:

    • Both OBJECT types have the same number of keys.

    • Both OBJECT types use the same names for keys.

    • The keys in both OBJECT types are in the same order.

    • The type of each value in one OBJECT type can be coerced to the type of the corresponding value in the other OBJECT type.

      As is the case with element types in structured ARRAY values, you can coerce the type of one value to another type only if:

      • Both types are numeric. The following cases are supported:

        • Both use the same numeric type but possibly differ in precision and/or scale.

        • Coercing NUMBER to FLOAT (and vice versa).

      • Both types are timestamps. The following cases are supported:

        • Both use the same type but possibly differ in precision.

        • Coercing TIMESTAMP_LTZ to TIMESTAMP_TZ (and vice versa).

    For example:

    • An OBJECT(city VARCHAR, zipcode NUMBER) value can be coerced to an OBJECT(city VARCHAR, zipcode DOUBLE) value.

    • An OBJECT(city VARCHAR, zipcode NUMBER) value can’t be coerced to an OBJECT(city VARCHAR, zipcode DATE) value.

  • A MAP value with one value type can be coerced to a MAP value with a different value type if:

    • Both value types are numeric. The following cases are supported:

      • Both use the same numeric type but possibly differ in precision and/or scale.

      • Coercing NUMBER to FLOAT (and vice versa).

    • Both value types are timestamps. The following cases are supported:

      • Both use the same type but possibly differ in precision.

      • Coercing TIMESTAMP_LTZ to TIMESTAMP_TZ (and vice versa).

    For example, a MAP(VARCHAR, NUMBER) value can be coerced to a MAP(VARCHAR, DOUBLE) value.

  • A MAP value with one key type can be coerced to a MAP value with a different key type if both key types use the same integer NUMERIC type that differ only in precision.

    For example, a MAP(VARCHAR, NUMBER) value can’t be coerced to a MAP(NUMBER, NUMBER) value.

• A structured type value can’t be coerced to a semi-structured value (and vice versa).

• A VARCHAR value can’t be coerced to a structured type value.

Casting from one structured type to another¶

You can call the CAST function or use the :: operator to cast from a value of one structured type to a value of another structured type. You can cast values from and to the following structured types:

• For structured ARRAYs:

  You can cast an ARRAY value of one type to an ARRAY value of another type.

• For structured OBJECTs:

  • You can use a cast to change the order of key-value pairs in an OBJECT value.

  • You can use a cast to change the names of the keys in an OBJECT value.

  • You can use a cast to add keys to an OBJECT value.

  • You can cast a structured OBJECT value to a MAP value.

• For MAP values:

  • You can cast a MAP value with keys and values of a specific type to a MAP value with keys and values of a different type.

  • You can cast a MAP value to a structured OBJECT value.

If it isn’t possible to cast the values from one type to the other, the cast fails. For example, attempting to cast an ARRAY(BOOLEAN) value to an ARRAY(DATE) value fails.

SELECT CAST(
  CAST([1,2,3] AS ARRAY(NUMBER))
  AS ARRAY(VARCHAR)) AS cast_array;

+------------+
| CAST_ARRAY |
|------------|
| [          |
|   "1",     |
|   "2",     |
|   "3"      |
| ]          |
+------------+

SELECT CAST(
  {'city': 'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  AS OBJECT(state VARCHAR, city VARCHAR)) AS object_value_order;

+-----------------------+
| OBJECT_VALUE_ORDER    |
|-----------------------|
| {                     |
|   "state": "CA",      |
|   "city": "San Mateo" |
| }                     |
+-----------------------+

SELECT CAST({'city':'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  AS OBJECT(city_name VARCHAR, state_name VARCHAR) RENAME FIELDS) AS object_value_key_names;

+-----------------------------+
| OBJECT_VALUE_KEY_NAMES      |
|-----------------------------|
| {                           |
|   "city_name": "San Mateo", |
|   "state_name": "CA"        |
| }                           |
+-----------------------------+

SELECT CAST({'city':'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  AS OBJECT(city VARCHAR, state VARCHAR, zipcode NUMBER) ADD FIELDS) AS add_fields;

+------------------------+
| ADD_FIELDS             |
|------------------------|
| {                      |
|   "city": "San Mateo", |
|   "state": "CA",       |
|   "zipcode": null      |
| }                      |
+------------------------+

Using SQL functions to construct structured ARRAY and OBJECT values¶

The following functions construct semi-structured ARRAY values:

• ARRAY_CONSTRUCT

• ARRAY_CONSTRUCT_COMPACT

• ARRAY_AGG

• TO_ARRAY

The following functions construct semi-structured OBJECT values:

• OBJECT_CONSTRUCT

• OBJECT_CONSTRUCT_KEEP_NULL

• OBJECT_AGG

• TO_OBJECT

To construct a structured ARRAY or OBJECT value, use these functions and explicitly cast the return value of the function. For example:

SELECT ARRAY_CONSTRUCT(10, 20, 30)::ARRAY(NUMBER);

SELECT OBJECT_CONSTRUCT(
  'oname', 'abc',
  'created_date', '2020-01-18'::DATE
)::OBJECT(
  oname VARCHAR,
  created_date DATE
);

For details, refer to Explicitly casting a semi-structured type to a structured type.

Using ARRAY and OBJECT constants to construct structured ARRAY and OBJECT values¶

When you specify an ARRAY constant or an OBJECT constant, you are specifying a semi-structured ARRAY or OBJECT value.

To construct a structured ARRAY or OBJECT value, you must explicitly cast the expression. For example:

SELECT [10, 20, 30]::ARRAY(NUMBER);

SELECT {
  'oname': 'abc',
  'created_date': '2020-01-18'::DATE
}::OBJECT(
  oname VARCHAR,
  created_date DATE
);

For details, refer to Explicitly casting a semi-structured type to a structured type.

Constructing a MAP value¶

To construct a MAP value, construct a semi-structured OBJECT value, and cast the OBJECT value to a MAP value.

For example, the following statements both produce the MAP value {'city'->'San Mateo','state'->'CA'}:

SELECT OBJECT_CONSTRUCT(
  'city', 'San Mateo',
  'state', 'CA'
)::MAP(
  VARCHAR,
  VARCHAR
);

SELECT {
  'city': 'San Mateo',
  'state': 'CA'
}::MAP(
  VARCHAR,
  VARCHAR
);

The following statement produces the MAP value {-10->'CA',-20->'OR'}:

SELECT {
  '-10': 'CA',
  '-20': 'OR'
}::MAP(
  NUMBER,
  VARCHAR
);

For details, refer to Casting semi-structured OBJECT and VARIANT values to MAP values.

Getting the list of keys from a structured OBJECT value¶

To get the list of keys in a structured OBJECT value, call the OBJECT_KEYS function:

SELECT OBJECT_KEYS({'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR, state VARCHAR));

If the input is a structured OBJECT value, the function returns an ARRAY(VARCHAR) value containing the keys. If the input is a semi-structured OBJECT value, the function returns an ARRAY value.

Getting the list of keys from a MAP value¶

To get the list of keys in a MAP value, call the MAP_KEYS function:

SELECT MAP_KEYS({'my_key':'my_value'}::MAP(VARCHAR,VARCHAR));

Accessing values and elements from values of structured types¶

You can use the following methods to access values and elements from structured ARRAY, structured OBJECT, and MAP values:

• The GET function

• The GET_IGNORE_CASE function

• The GET_PATH function

• Dot Notation

• Bracket Notation

The returned values and elements have the type specified for the structured value, rather than VARIANT.

The following example passes the first element of a semi-structured ARRAY value and an ARRAY(VARCHAR) value to the SYSTEM$TYPEOF function to return the data type of that element:

SELECT
  SYSTEM$TYPEOF(
    ARRAY_CONSTRUCT('San Mateo')[0]
  ) AS semi_structured_array_element,
  SYSTEM$TYPEOF(
    CAST(
      ARRAY_CONSTRUCT('San Mateo') AS ARRAY(VARCHAR)
    )[0]
  ) AS structured_array_element;

+-------------------------------+-----------------------------+
| SEMI_STRUCTURED_ARRAY_ELEMENT | STRUCTURED_ARRAY_ELEMENT    |
|-------------------------------+-----------------------------|
| VARIANT[LOB]                  | VARCHAR(16777216)[LOB]      |
+-------------------------------+-----------------------------+

Note the following:

• When you pass a structured OBJECT value to the GET or GET_IGNORE_CASE function, you must specify a constant for the key.

  You don’t need to specify a constant if you are passing a MAP or structured ARRAY value to the GET function.

  You also don’t need to specify a constant if you are passing a MAP value to the GET_IGNORE_CASE function.

• When you pass a structured OBJECT, structured ARRAY, or MAP value to the GET_PATH function, you must specify a constant for the path name.

• For a structured OBJECT value, if you use an OBJECT key or a path that doesn’t exist, a compile-time error occurs.

  In contrast, when you use an index, key, or path that doesn’t exist with a semi-structured OBJECT value, the function returns NULL.

Determining the size of a structured ARRAY value¶

To determine the size of a structured ARRAY value, pass the ARRAY value to the ARRAY_SIZE function:

SELECT ARRAY_SIZE([1,2,3]::ARRAY(NUMBER));

Determining the size of a MAP value¶

To determine the size of a MAP value, pass the MAP value to MAP_SIZE function:

SELECT MAP_SIZE({'my_key':'my_value'}::MAP(VARCHAR,VARCHAR));

Looking up elements in a structured ARRAY value¶

To determine if an element is present in a structured ARRAY value, call the ARRAY_CONTAINS function. For example:

SELECT ARRAY_CONTAINS(10, [1, 10, 100]::ARRAY(NUMBER));

To determine the position of an element in a structured ARRAY value, call the ARRAY_POSITION function. For example:

SELECT ARRAY_POSITION(10, [1, 10, 100]::ARRAY(NUMBER));

Determining if a MAP value contains a key¶

To determine if a MAP value contains a key, call the MAP_CONTAINS_KEY function:

For example:

SELECT MAP_CONTAINS_KEY('key_to_find', my_map);

SELECT MAP_CONTAINS_KEY(10, my_map);

Comparing structured values with semi-structured values¶

You can’t compare a structured ARRAY, structured OBJECT, or MAP value with a semi-structured ARRAY, OBJECT, or VARIANT value.

Comparing structured values with other structured values¶

You can compare two values of the same type (for example, two structured ARRAY values, two structured OBJECT values, or two MAP values).

Currently, the following comparison operators are supported for comparing values of structured types:

• =

• !=

• <

• <=

• >=

• >

When you compare two structured values for equality, note the following:

• If one type can’t be coerced to the other type, the comparison fails.

• When you compare MAP values that have numeric keys, the keys are compared as numbers (not as VARCHAR values).

When you compare two structured values using <, <=, >=, or >, the structured value fields are compared in alphabetical order. For example, the following value:

{'a':2,'b':1}::OBJECT(b INTEGER,a INTEGER)

is greater than:

{'a':1,'b':2}::OBJECT(b INTEGER,a INTEGER)

Determining if two ARRAY values overlap¶

To determine if the elements of two structured ARRAY values overlap, call the ARRAYS_OVERLAP function. For example:

SELECT ARRAYS_OVERLAP(numeric_array, other_numeric_array);

The ARRAY values must be of comparable types.

You can’t pass a semi-structured ARRAY value and a structured ARRAY value to this function. Both ARRAY values must either be structured or semi-structured.

Transforming structured ARRAY values¶

When you pass a structured ARRAY value to these functions, the functions return a structured ARRAY value of the same type:

• ARRAY_APPEND

• ARRAY_CAT

• ARRAY_COMPACT

• ARRAY_EXCEPT

• ARRAY_INSERT

• ARRAY_INTERSECTION

• ARRAY_PREPEND

• ARRAY_SLICE

• ARRAY_UNION_AGG

The next sections explain how these functions work with structured ARRAY values.

• Functions that add elements to ARRAY values

• Functions that accept multiple ARRAY values as input

SELECT ARRAY_APPEND( [1,2]::ARRAY(DOUBLE), 3::NUMBER );

SELECT ARRAY_APPEND( [1,2]::ARRAY(DOUBLE), '3' );

SELECT ARRAY_APPEND( [1,2]::ARRAY(NUMBER), '2022-02-02'::DATE );

SELECT ARRAY_CAT( [1,2]::ARRAY(NUMBER), ['3','4'] );

SELECT ARRAY_CAT( [1,2], ['3','4']::ARRAY(VARCHAR) );

SELECT
  ARRAY_CAT(
    [1, 2, 3]::ARRAY(NUMBER NOT NULL),
    [5.5, NULL]::ARRAY(NUMBER(2, 1))
  ) AS concatenated_array,
  SYSTEM$TYPEOF(concatenated_array);

+--------------------+-----------------------------------+
| CONCATENATED_ARRAY | SYSTEM$TYPEOF(CONCATENATED_ARRAY) |
|--------------------+-----------------------------------|
| [                  | ARRAY(NUMBER(38,1))[LOB]          |
|   1,               |                                   |
|   2,               |                                   |
|   3,               |                                   |
|   5.5,             |                                   |
|   undefined        |                                   |
| ]                  |                                   |
+--------------------+-----------------------------------+

SELECT ARRAY_EXCEPT( [1,2]::ARRAY(NUMBER), [2,3]::ARRAY(DOUBLE) );

SELECT ARRAY_EXCEPT( [1,2]::ARRAY(NUMBER), ['2','3']::ARRAY(VARCHAR) );

Transforming structured OBJECT values¶

The following sections explain how to return a structured OBJECT value that has been transformed from another OBJECT value:

• Removing key-value pairs

• Inserting key-value pairs and updating values

• Selecting key-value pairs from an existing OBJECT

To change the order of key-value pairs, rename keys, or add keys without specifying values, use the CAST function or :: operator. For details, see Casting from one structured type to another.

Transforming MAP values¶

To transform MAP values, use the following functions:

• MAP_CAT

• MAP_DELETE

• MAP_INSERT

• MAP_PICK

Using the FLATTEN function with values of structured types¶

You can pass structured ARRAY, structured OBJECT, and MAP values to the FLATTEN function. As is the case with semi-structured data types, you can use the PATH argument to specify the value being flattened.

• If the value being flattened is a structured ARRAY value and the RECURSIVE argument is FALSE, the value column contains a value of the same type as the ARRAY value.

  For example:

  SELECT value, SYSTEM$TYPEOF(value)
    FROM TABLE(FLATTEN(INPUT => [1.08, 2.13, 3.14]::ARRAY(DOUBLE)));
  

  Copy

  +-------+----------------------+
  | VALUE | SYSTEM$TYPEOF(VALUE) |
  |-------+----------------------|
  |  1.08 | FLOAT[DOUBLE]        |
  |  2.13 | FLOAT[DOUBLE]        |
  |  3.14 | FLOAT[DOUBLE]        |
  +-------+----------------------+
  

• If the value being flattened is a MAP value and the RECURSIVE argument is FALSE, the key column contains a key of the same type as the MAP key, and the value column contains a value of the same type as the MAP value.

  For example:

  SELECT key, SYSTEM$TYPEOF(key), value, SYSTEM$TYPEOF(value)
    FROM TABLE(FLATTEN(INPUT => {'my_key': 'my_value'}::MAP(VARCHAR, VARCHAR)));
  

  Copy

  +--------+------------------------+----------+------------------------+
  | KEY    | SYSTEM$TYPEOF(KEY)     | VALUE    | SYSTEM$TYPEOF(VALUE)   |
  |--------+------------------------+----------+------------------------|
  | my_key | VARCHAR(16777216)[LOB] | my_value | VARCHAR(16777216)[LOB] |
  +--------+------------------------+----------+------------------------+
  

• Otherwise, the key and value columns have the type VARIANT.

For MAP values, the order of keys and values returned is indeterminate.

Using the PARSE_JSON function¶

The PARSE_JSON function doesn’t return structured types.

Using structured types with set operators and CASE expressions¶

You can use structured ARRAY, structured OBJECT, and MAP values in:

• Query expressions combined by set operators (e.g. UNION ALL).

• CASE expressions.

For set operators, if different types are used in the different expressions (for example, if one type is ARRAY(NUMBER) and the other is ARRAY(DOUBLE)), one type is coerced to the other.

Working with other semi-structured functions¶

The following functions don’t accept a structured ARRAY, structured OBJECT, or MAP values as an input argument:

• AS_ARRAY

• AS_OBJECT

• IS_ARRAY

• IS_OBJECT

• TYPEOF

Passing a structured type value as input results in an error.

Using the SHOW COLUMNS command to view structured type information¶

In the output of the SHOW COLUMNS command, the data_type column includes information about the types of elements, keys, and values.

Using the DESCRIBE and other SHOW commands to view structured type information¶

The output of the following commands includes information about structured types:

• DESCRIBE TABLE

• DESCRIBE RESULT

• DESCRIBE FUNCTION

• DESCRIBE PROCEDURE

• SHOW FUNCTIONS

• SHOW PROCEDURES

For example, in the DESCRIBE RESULT output, the row for a MAP(VARCHAR, VARCHAR) column contains the following value in the type column:

map(VARCHAR(16777216), VARCHAR(16777216))

The row for an ARRAY(NUMBER) column contains the following value in the type column:

ARRAY(NUMBER(38,0))

Viewing information about the structured types used in a database¶

For columns of structured types, the INFORMATION_SCHEMA COLUMNS view only provides information about the basic data type of the column (ARRAY, OBJECT, or MAP).

For example, the data_type column just contains ARRAY, OBJECT, or MAP. The column doesn’t include the types of the elements, keys, or values.

To view information about the types of elements, keys, and values, use the following views:

• For information about the types of elements in structured ARRAY types, query the ELEMENT_TYPES view in INFORMATION_SCHEMA or the ELEMENT_TYPES view in ACCOUNT_USAGE.

• For information about the types of keys and values in structured OBJECT and MAP types, query the FIELDS view in INFORMATION_SCHEMA or the FIELDS view in ACCOUNT_USAGE.