Structured data types¶

Specifying a structured ARRAY¶

To specify a structured ARRAY, 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 cannot specify a VARIANT, semi-structured ARRAY, or semi-structured OBJECT as the type of the ARRAY element.

• NOT NULL specifies that the ARRAY cannot 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 to a structured ARRAY (an ARRAY of NUMBER elements).

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

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¶

To specify a structured OBJECT, use the following syntax:

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

Where:

• key specifies a key for the OBJECT.

  • Each key in an object definition must be unique.

  • The order of the keys is part of the object definition. Compare two OBJECTs that have the same keys in a different order is not allowed. (A compile time error occurs.)

  • If you do not specify any key but specify the parentheses (i.e. 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 cannot 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 cannot 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 to a structured OBJECT 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.

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), num NUMBER(38,0) NOT NULL )[LOB] | OBJECT[LOB]            |
+----------------------------------------------------------------+------------------------+

Specifying a MAP¶

To specify a MAP, 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 cannot use a floating point data type as the type for the key.

  Note that map keys cannot 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 cannot 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 cannot be NULL. NOT NULL specifies that the value cannot be NULL.

The following example casts a semi-structured OBJECT to a MAP and uses the SYSTEM$TYPEOF function to print the resulting type of the object. 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 semi-structured object to a structured type, you can call the CAST function or use the :: operator.

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

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

• Casting semi-structured ARRAYs and VARIANTs to structured ARRAYs

• Casting semi-structured OBJECTs and VARIANTs to structured OBJECTs

• Casting semi-structured OBJECTs and VARIANTs to MAPs

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 structured type to 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):

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

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

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

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

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

• If col_map is MAP(VARCHAR, VARCHAR):

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

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

Note the following:

• When you are casting to a semi-structured OBJECT, the order of keys in the structured OBJECT is not preserved.

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

  If you are casting a structured ARRAY to a VARIANT, 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 that uses a NUMBER type for keys, the MAP keys are converted to strings in the returned OBJECT.

Implicit casting a value (coercion)¶

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

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

  • An ARRAY of one type can be coerced to an ARRAY 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) can be coerced to an ARRAY(DOUBLE).

    • An ARRAY(DATE) cannot be coerced to an ARRAY(NUMBER).

  • An OBJECT with one type definition can be coerced to an OBJECT 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 ARRAYs, 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) can be coerced to an OBJECT(city VARCHAR, zipcode DOUBLE).

    • An OBJECT(city VARCHAR, zipcode NUMBER) cannot be coerced to an OBJECT(city VARCHAR, zipcode DATE).

  • A MAP with one value type can be coerced to a MAP 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) can be coerced to a MAP(VARCHAR, DOUBLE).

  • A MAP with one key type can be coerced to a MAP 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) cannot be coerced to a MAP(NUMBER, NUMBER).

• A structured type cannot be coerced to a semi-structured object (and vice versa).

• A VARCHAR value cannot be coerced to a structured type.

Casting from one structured type to another¶

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

• For structured ARRAYs:

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

• For structured OBJECTs:

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

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

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

  • You can cast a structured OBJECT to a MAP.

• For MAPs:

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

  • You can cast a MAP to a structured OBJECT.

If it is not possible to cast the values from one type to the other, the cast fails. For example, attempting to cast an ARRAY(BOOLEAN) to an ARRAY(DATE) fails.

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

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

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

+------------------------------------------------------------------------------+
| CAST({'CITY':'SAN MATEO','STATE': 'CA'}::OBJECT(CITY VARCHAR, STATE VARCHAR) |
|   AS OBJECT(CITY_NAME VARCHAR, STATE_NAME VARCHAR) RENAME FIELDS)            |
|------------------------------------------------------------------------------|
| {                                                                            |
|   "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);

+------------------------------------------------------------------------------+
| CAST({'CITY':'SAN MATEO','STATE': 'CA'}::OBJECT(CITY VARCHAR, STATE VARCHAR) |
|   AS OBJECT(CITY VARCHAR, STATE VARCHAR, ZIPCODE NUMBER) ADD FIELDS)         |
|------------------------------------------------------------------------------|
| {                                                                            |
|   "city": "San Mateo",                                                       |
|   "state": "CA",                                                             |
|   "zipcode": null                                                            |
| }                                                                            |
+------------------------------------------------------------------------------+

Using SQL functions to construct structured ARRAYs and OBJECTs¶

The following functions construct semi-structured ARRAYs:

• ARRAY_CONSTRUCT

• ARRAY_CONSTRUCT_COMPACT

• ARRAY_AGG

• TO_ARRAY

The following functions construct semi-structured OBJECTs:

• OBJECT_CONSTRUCT

• OBJECT_CONSTRUCT_KEEP_NULL

• OBJECT_AGG

• TO_OBJECT

To construct a structured ARRAY or OBJECT, 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(
  'name', 'abc',
  'created_date', '2020-01-18'::DATE
)::OBJECT(
  name 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 ARRAYs and OBJECTs¶

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

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

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

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

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

Constructing a MAP¶

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

For example, the following statements both produce the MAP {'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 {-10->'CA',-20->'OR'}:

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

For details, refer to Casting semi-structured OBJECTs and VARIANTs to MAPs.

Getting the list of keys from a structured OBJECT¶

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

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

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

Getting the list of keys from a MAP¶

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

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

Accessing values and elements from structured types¶

You can use the following methods to access values and elements from structured ARRAYs, structured OBJECTs, and MAPs:

• 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 object, rather than VARIANT.

The following example passes the first element of a semi-structured ARRAY and an ARRAY(VARCHAR) 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 to the GET or GET_IGNORE_CASE function, you must specify a constant for the key.

  You do not need to specify a constant if you are passing a MAP or structured ARRAY to the GET function.

  You also do not need to specify a constant if you are passing a MAP to the GET_IGNORE_CASE function.

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

• For a structured object, If you use an OBJECT key or a path that does not exist, a compile-time error occurs.

  In contrast, when you use an index, key, or path that does not exist with a semi-structured object, the function returns NULL.

Determining the size of a structured ARRAY¶

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

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

Determining the size of a MAP¶

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

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

Looking up elements in a structured ARRAY¶

To determine if an element is present in a structured ARRAY, 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, call the ARRAY_POSITION function. For example:

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

Determining if a MAP contains a key¶

To determine if a MAP 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 objects with semi-structured objects¶

You cannot compare a structured ARRAY, structured OBJECT, or MAP with a semi-structured ARRAY, OBJECT, or VARIANT.

Comparing structured objects with other structured objects¶

You can compare two objects of the same type (e.g. two structured ARRAYs, two structured OBJECTs, or two MAPs).

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

• =

• !=

• <

• <=

• >=

• >

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

• If one type cannot be coerced to the other type, the comparison fails.

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

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

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

is greater than:

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

Determining if two ARRAYs overlap¶

If you need to determine if the elements of two structured ARRAYs overlap, call the ARRAYS_OVERLAP function. For example:

SELECT ARRAYS_OVERLAP(numeric_array, other_numeric_array);

The ARRAYs must be of comparable types.

Note that you cannot pass a semi-structured ARRAY and a structured ARRAY to this function. Both ARRAYs must either be structured or semi-structured.

Transforming structured ARRAYs¶

When pass a structured ARRAY to these functions, the functions return a structured ARRAY 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 ARRAYs.

• Functions that add elements to arrays

• Functions that accept multiple ARRAYs 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 OBJECTs¶

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

• Removing key-value pairs

• Inserting key-value pairs and updating values

• Selecting key-value pairs from an existing OBJECT

If you need 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 MAPs¶

To transform MAPs, use the following functions:

• MAP_CAT

• MAP_DELETE

• MAP_INSERT

• MAP_PICK

Using the FLATTEN function with structured types¶

You can pass structured ARRAYs, structured OBJECTs, and MAPs 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 and the RECURSIVE argument is FALSE, the value column contains a value of the same type as the ARRAY.

  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 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 MAPs, the order of keys and values returned is indeterminate.

Using the PARSE_JSON function¶

Note that the PARSE_JSON function does not return structured types.

Using structured types with set operators and CASE expressions¶

You can use structured ARRAYs, structured OBJECTs, and MAPs 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 (e.g. if one type is ARRAY(NUMBER) and the other is ARRAY(DOUBLE)), one type will be coerced to the other.

Working with other semi-structured functions¶

The following functions do not accept a structured ARRAY, structured OBJECT, or MAP as an input argument:

• AS_ARRAY

• AS_OBJECT

• IS_ARRAY

• IS_OBJECT

• TYPEOF

Passing a structured type 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 does not 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 ARRAYs, 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 OBJECTs and MAPs, query the FIELDS view in INFORMATION_SCHEMA or the FIELDS view in ACCOUNT_USAGE.