Structured Data Types

A structured type is an ARRAY, OBJECT, or MAP that contains elements or key-value pairs with specific Snowflake data types. The following are examples of structured types:

  • An ARRAY of INTEGER elements.

  • An OBJECT with VARCHAR and NUMBER key-value pairs.

  • A MAP that associates a VARCHAR key with a DOUBLE value.

You can use structured types in the following ways:

  • You can define a structured type column in an Iceberg table.

    The Iceberg data types list, struct, and map correspond to the structured ARRAY, structured OBJECT, and MAP types in Snowflake.

    A structured type column supports a maximum of 1000 sub-columns.

  • You use structured types when accessing data from a structured type column in an Iceberg table.

  • You can cast a semi-structured ARRAY, OBJECT, or VARIANT to a corresponding structured type (e.g. an ARRAY to an ARRAY of INTEGER elements). You can also cast a structured type of a semi-structured type.

Note

Currently, tables other than Iceberg tables do not support structured types. You cannot add a column of a structured type to a regular table.

This topic explains how to use structured types in Snowflake.

Specifying a Structured Type

When defining a structured type column or casting a value to a structured type, use the syntax described in the following sections:

Specifying a Structured ARRAY

To specify a structured ARRAY, use the following syntax:

ARRAY( <element_type> [ NOT NULL ] )
Copy

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;
Copy
+-------------------------------+-----------------------+
| 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 ] ]
    [ , ... ]
  ]
)
Copy

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;
Copy
+----------------------------------------------------------------+------------------------+
| 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 ] )
Copy

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;
Copy
+------------------------------------------------+
| MAP_EXAMPLE                                    |
|------------------------------------------------|
| map(VARCHAR(16777216), VARCHAR(16777216))[LOB] |
+------------------------------------------------+

Using Structured Types in Semi-Structured Types

You cannot use a MAP, structured OBJECT, or structured ARRAY in a VARIANT, semi-structured OBJECT, or semi-structured ARRAY. An error occurs in the following situations:

Converting Structured and Semi-Structured Types

The following table summarizes rules for converting structured OBJECTs, structured ARRAYs, and MAPs to semi-structured OBJECTs, ARRAYs, and VARIANTs (and vice versa).

Source Data Type

Target Data Type

Castable

Coercible

Semi-Structured ARRAY

Structured ARRAY

Semi-Structured OBJECT

  • Structured OBJECT

  • MAP

Semi-Structured VARIANT

  • Structured ARRAY

  • Structured OBJECT

  • MAP

Structured ARRAY

Semi-Structured ARRAY

  • Structured OBJECT

  • MAP

Semi-Structured OBJECT

  • Structured ARRAY

  • Structured OBJECT

  • MAP

Semi-Structured VARIANT

The following sections explain these rules in more detail.

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.

Note

TRY_CAST is not supported for structured types.

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

Semi-Structured Type

Structured Type That You Can Cast To

ARRAY

Structured ARRAY

OBJECT

MAP or structured OBJECT

VARIANT

MAP or structured ARRAY or OBJECT

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

Casting Semi-Structured ARRAYs and VARIANTs to Structured ARRAYs

The following steps demonstrate how to cast a semi-structured ARRAY or VARIANT to an ARRAY of NUMBER elements:

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;
Copy

Or:

SELECT
  SYSTEM$TYPEOF(
    [1,2,3]::ARRAY(NUMBER)
  ) AS array_cast_type,
  SYSTEM$TYPEOF(
    [1,2,3]::VARIANT::ARRAY(NUMBER)
  ) AS variant_cast_type;
Copy
+--------------------------+--------------------------+
| ARRAY_CAST_TYPE          | VARIANT_CAST_TYPE        |
|--------------------------+--------------------------|
| ARRAY(NUMBER(38,0))[LOB] | ARRAY(NUMBER(38,0))[LOB] |
+--------------------------+--------------------------+

When you cast a semi-structured ARRAY or VARIANT to a structured ARRAY, note the following:

  • Each element of the ARRAY is cast to the specified type of the ARRAY.

    Casting the ARRAY column to ARRAY(VARCHAR) converts each value to a VARCHAR:

    SELECT
      CAST ([1,2,3] AS ARRAY(VARCHAR)) AS varchar_array,
      SYSTEM$TYPEOF(varchar_array) AS array_cast_type;
    
    Copy
    +---------------+-------------------------------+
    | VARCHAR_ARRAY | ARRAY_CAST_TYPE               |
    |---------------+-------------------------------|
    | [             | ARRAY(VARCHAR(16777216))[LOB] |
    |   "1",        |                               |
    |   "2",        |                               |
    |   "3"         |                               |
    | ]             |                               |
    +---------------+-------------------------------+
    
  • If the element cannot be cast to the specified type (e.g. casting ['a', 'b', 'c'] to ARRAY(NUMBER)), the cast fails.

  • If the ARRAY contains NULL elements and the ARRAY type specifies NOT NULL (e.g. casting [1, NULL, 3] to ARRAY(NUMBER NOT NULL), the cast fails.

  • Elements that are JSON null values are converted to NULL, if the target element type does not support JSON nulls (i.e. the target type is not a semi-structured ARRAY, OBJECT, or VARIANT).

    For example, if you are casting to ARRAY(NUMBER), JSON null values are converted to NULL because NUMBER does not support JSON nulls.

    On the other hand, if you are casting to ARRAY(VARIANT), JSON null values are not converted to NULL because VARIANT supports JSON nulls.

Casting Semi-Structured OBJECTs and VARIANTs to Structured OBJECTs

The following steps demonstrate how to cast a semi-structured OBJECT or VARIANT to a structured OBJECT containing the city and state key-value pairs (which are VARCHAR values):

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;
Copy

Or:

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;
Copy
+--------------------------------------------------------------+--------------------------------------------------------------+
| OBJECT_CAST_TYPE                                             | VARIANT_CAST_TYPE                                            |
|--------------------------------------------------------------+--------------------------------------------------------------|
| OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216))[LOB] |
+--------------------------------------------------------------+--------------------------------------------------------------+

When you cast a semi-structured OBJECT or VARIANT to a structured OBJECT, note the following:

  • The OBJECT must not contain any additional keys that are not specified in the OBJECT type.

    If there are additional keys, the cast fails.

  • If the OBJECT is missing a key that is specified in the OBJECT type, the cast fails.

  • The value of each key in the OBJECT is converted to the specified type for that key.

    If a value cannot be cast to the specified type, the cast fails.

  • If the value for a key is a JSON null value, the value is converted to NULL, if the target value type does not support JSON nulls (i.e. the target type is not a semi-structured ARRAY, OBJECT, or VARIANT).

    For example, if you are casting to OBJECT(city VARCHAR), JSON null values are converted to NULL because VARCHAR does not support JSON nulls.

    On the other hand, if you are casting to OBJECT(city VARIANT), JSON null values are not converted to NULL because VARIANT supports JSON nulls.

Casting Semi-Structured OBJECTs and VARIANTs to MAPs

The following statements demonstrate how to cast a semi-structured OBJECT or VARIANT to a MAP that associates a VARCHAR key with a VARCHAR value:

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;
Copy

Or:

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;
Copy
+------------------------------------------------+------------------------------------------------+
| MAP_CAST_TYPE                                  | VARIANT_CAST_TYPE                              |
|------------------------------------------------+------------------------------------------------|
| map(VARCHAR(16777216), VARCHAR(16777216))[LOB] | map(VARCHAR(16777216), VARCHAR(16777216))[LOB] |
+------------------------------------------------+------------------------------------------------+

When you cast a semi-structured OBJECT or VARIANT to a MAP, note the following:

  • If the keys and values do not match the specified types, the keys and values are converted to the specified types.

  • If the keys and values cannot be cast to the specified types, the cast fails.

  • If the value for a key is a JSON null value, the value is converted to NULL, if the target value type does not support JSON nulls (i.e. the target type is not a semi-structured ARRAY, OBJECT, or VARIANT).

    For example, if you are casting to MAP(VARCHAR, VARCHAR), JSON null values are converted to NULL because VARCHAR does not support JSON nulls.

    On the other hand, if you are casting to MAP(VARCHAR, VARIANT), JSON null values are not converted to NULL because VARIANT supports JSON nulls.

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).

Note

TRY_CAST is not supported with structured types.

Structured Type

Semi-Structured Type That You Can Cast To

Structured ARRAY

ARRAY

MAP or structured OBJECT

OBJECT

MAP, structured ARRAY, or structured OBJECT

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:

Note

TRY_CAST is not supported with structured types.

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.

Example: Casting from One Type of ARRAY to Another

The following example casts an ARRAY(NUMBER) to an ARRAY(VARCHAR):

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

Example: Changing the Order of Key-Value Pairs in an OBJECT

The following example changes the order of key-value pairs in a structured OBJECT:

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

Example: Changing the Key Names in an OBJECT

To change the key names in a structured OBJECT, specify the RENAME FIELDS keywords at the end of CAST. For example:

SELECT CAST({'city':'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  AS OBJECT(city_name VARCHAR, state_name VARCHAR) RENAME FIELDS);
Copy
+------------------------------------------------------------------------------+
| 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"                                                         |
| }                                                                            |
+------------------------------------------------------------------------------+

Example: Adding Keys to an OBJECT

If the type that you are casting to has additional key-value pairs that are not present in the original structured object, specify the ADD FIELDS keywords at the end of CAST. For example:

SELECT CAST({'city':'San Mateo','state': 'CA'}::OBJECT(city VARCHAR, state VARCHAR)
  AS OBJECT(city VARCHAR, state VARCHAR, zipcode NUMBER) ADD FIELDS);
Copy
+------------------------------------------------------------------------------+
| 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                                                            |
| }                                                                            |
+------------------------------------------------------------------------------+

The values for the newly added keys will be set to NULL. If you want to assign a value to these keys, call the OBJECT_INSERT function instead.

Constructing Structured ARRAYs, Structured OBJECTs, and MAPs

The following sections explain how to construct structured ARRAYs, structured OBJECTs, and MAPs.

Using SQL Functions to Construct Structured ARRAYs and OBJECTs

The following functions construct semi-structured ARRAYs:

The following functions construct semi-structured OBJECTs:

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);
Copy
SELECT OBJECT_CONSTRUCT(
  'name', 'abc',
  'created_date', '2020-01-18'::DATE
)::OBJECT(
  name VARCHAR,
  created_date DATE
);
Copy

For details, refer to Explicitly Casting a Semi-Structured Type to a Structured Type.

Note

You cannot pass structured ARRAYs, structured OBJECTs, or MAPs to these functions. Doing so would result in a structured type being implicitly cast to a semi-structured type, which is not allowed (as noted in Implicit Casting a Value (Coercion)).

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);
Copy
SELECT {
  'name': 'abc',
  'created_date': '2020-01-18'::DATE
}::OBJECT(
  name VARCHAR,
  created_date DATE
);
Copy

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
);
Copy
SELECT {
  'city': 'San Mateo',
  'state': 'CA'
}::MAP(
  VARCHAR,
  VARCHAR
);
Copy

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

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

For details, refer to Casting Semi-Structured OBJECTs and VARIANTs to MAPs.

Working with Keys, Values, and Elements in Structured Types

The following sections explain how to use values and elements in structured types.

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));
Copy

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));
Copy

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 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;
Copy
+-------------------------------+-----------------------------+
| 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));
Copy

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));
Copy

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));
Copy

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));
Copy

Note

For both functions, use an element of a type that is comparable to the type of the ARRAY.

Do not cast the expression for the element to a VARIANT.

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);
Copy
SELECT MAP_CONTAINS_KEY(10, my_map);
Copy

Comparing Objects

The following sections explain how to compare objects:

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)
Copy

is greater than:

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

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);
Copy

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 Types

The following sections explain how to transform structured ARRAYs, structured OBJECTs, and MAPs:

Transforming Structured ARRAYs

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

The next sections explain how these functions work with structured ARRAYs.

Functions That Add Elements to Arrays

The following functions add elements to an ARRAY:

For these functions, the type of the element must be coercible to the type of the ARRAY.

For example, the following call succeeds because NUMBER can be coerced to DOUBLE (the type of the ARRAY):

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

The following call succeeds because VARCHAR can be coerced to DOUBLE:

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

The following call fails because DATE cannot be coerced to NUMBER:

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

Functions That Accept Multiple ARRAYs as Input

The following functions accept multiple ARRAYs as input arguments:

When you call these functions, both arguments must either be structured ARRAYs or semi-structured ARRAYs. For example, the following calls fail because one argument is a structured ARRAY and the other argument is a semi-structured ARRAY:

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

The ARRAY_EXCEPT function returns an ARRAY of the same type as the ARRAY in the first argument.

The ARRAY_CAT and ARRAY_INTERSECTION functions return an ARRAY of a type that can accommodate both input types.

For example, the following call to ARRAY_CAT passes in two structured ARRAYs:

  • The first structured ARRAY does not allow NULLs and contains NUMBER values with the scale of 0. (NUMBER is NUMBER(38, 0).)

  • The second structured ARRAY contains a NULL and a number that has the scale of 1.

The ARRAY returned by ARRAY_CAT allows NULLs and contains NUMBER values with the scale of 1.

SELECT
  ARRAY_CAT(
    [1, 2, 3]::ARRAY(NUMBER NOT NULL),
    [5.5, NULL]::ARRAY(NUMBER(2, 1))
  ) AS concatenated_array,
  SYSTEM$TYPEOF(concatenated_array);
Copy
+--------------------+-----------------------------------+
| CONCATENATED_ARRAY | SYSTEM$TYPEOF(CONCATENATED_ARRAY) |
|--------------------+-----------------------------------|
| [                  | ARRAY(NUMBER(38,1))[LOB]          |
|   1,               |                                   |
|   2,               |                                   |
|   3,               |                                   |
|   5.5,             |                                   |
|   undefined        |                                   |
| ]                  |                                   |
+--------------------+-----------------------------------+

For the ARRAY_CAT function, the ARRAY in the second argument must be coercible to the type in the first argument.

For the ARRAY_EXCEPT and ARRAY_INTERSECTION functions, the ARRAY in the second argument must be comparable to the ARRAY in the first argument.

For example, the following call succeeds because ARRAY(NUMBER) is comparable to ARRAY(DOUBLE):

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

The following call fails because ARRAY(NUMBER) is not comparable to ARRAY(VARCHAR):

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

Transforming Structured OBJECTs

The following sections explain how to return a structured OBJECT that has been transformed from another 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.

Removing Key-Value Pairs

To return a new object that contains the key-value pairs from an existing object with specific key-value pairs removed, call the OBJECT_DELETE function.

When calling this function, note the following:

  • For the arguments that are keys, you must specify constants.

  • If the specified key is not part of the OBJECT type definition, the call fails. For example, the following call fails because the OBJECT does not contain the specified key zip_code:

    SELECT OBJECT_DELETE( {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR), 'zip_code' );
    
    Copy
    093201 (23001): Function OBJECT_DELETE: expected structured object to contain field zip_code but it did not.
    
  • The function returns a structured OBJECT. The type of the OBJECT excludes the deleted key. For example, suppose that you remove the city key:

    SELECT
      OBJECT_DELETE(
        {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
        'city'
      ) AS new_object,
      SYSTEM$TYPE_OF(new_object);
    
    Copy

    The function returns an OBJECT of the type OBJECT(state VARCHAR), which does not include the city key.

    +-----------------+--------------------------------------+
    | NEW_OBJECT      | SYSTEM$TYPEOF(NEW_OBJECT)            |
    |-----------------+--------------------------------------|
    | {               | OBJECT(state VARCHAR(16777216))[LOB] |
    |   "state": "CA" |                                      |
    | }               |                                      |
    +-----------------+--------------------------------------+
    
  • If the function removes all keys from the object, the function returns an empty structured OBJECT of the type OBJECT().

    SELECT
      OBJECT_DELETE(
        {'state':'CA'}::OBJECT(state VARCHAR),
        'state'
      ) AS new_object,
      SYSTEM$TYPEOF(new_object);
    
    Copy
    +------------+---------------------------+
    | NEW_OBJECT | SYSTEM$TYPEOF(NEW_OBJECT) |
    |------------+---------------------------|
    | {}         | OBJECT()[LOB]             |
    +------------+---------------------------+
    

    When the type of a structured OBJECT includes key-value pairs, the names and types of those pairs are included in parentheses in the type (for example, OBJECT(city VARCHAR)). Because an empty structured OBJECT contains no key-value pairs, the parentheses are empty.

Inserting Key-Value Pairs and Updating Values

To return a new object that contains the key-value pairs from an existing object with additional key-value pairs or new values for keys, call the OBJECT_INSERT function.

When calling this function, note the following:

  • For the arguments that are keys, you must specify constants.

  • When the updateFlag argument is FALSE (when you are inserting a new key-value pair):

    • If you specify a key that already exists in the OBJECT, an error occurs.

      SELECT OBJECT_INSERT(
        {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
        'city',
        'San Jose',
        false
      );
      
      Copy
      093202 (23001): Function OBJECT_INSERT:
        expected structured object to not contain field city but it did.
      
    • The function returns a structured OBJECT. The type of the OBJECT includes the newly inserted key. For example, suppose that you add the zipcode key with the DOUBLE value 94402:

      SELECT
        OBJECT_INSERT(
          {'city':'San Mateo','state':'CA'}::OBJECT(city VARCHAR,state VARCHAR),
          'zip_code',
          94402::DOUBLE,
          false
        ) AS new_object,
        SYSTEM$TYPEOF(new_object);
      
      Copy

      The function returns an OBJECT of the type OBJECT(city VARCHAR, state VARCHAR, zipcode DOUBLE):

      +-------------------------------------+---------------------------------------------------------------------------------------+
      | NEW_OBJECT                          | SYSTEM$TYPEOF(NEW_OBJECT)                                                             |
      |-------------------------------------+---------------------------------------------------------------------------------------|
      | {                                   | OBJECT(city VARCHAR(16777216), state VARCHAR(16777216), zip_code FLOAT NOT NULL)[LOB] |
      |   "city": "San Mateo",              |                                                                                       |
      |   "state": "CA",                    |                                                                                       |
      |   "zip_code": 9.440200000000000e+04 |                                                                                       |
      | }                                   |                                                                                       |
      +-------------------------------------+---------------------------------------------------------------------------------------+
      

      The type of the inserted value determines the type added to the OBJECT type definition. In this case, the value for zipcode is a value cast to a DOUBLE, so the type of zipcode is DOUBLE.

  • When the updateFlag argument is TRUE (when you are replacing an existing key-value pair):

    • If you specify a key that does not exist in the OBJECT, an error occurs.

    • The function returns a structured OBJECT of the same type.

    • The type of the inserted value is coerced to the type of the existing key.

Selecting Key-Value Pairs From an Existing OBJECT

To return a new object that contains selected key-value pairs from an existing object, call the OBJECT_PICK function.

When calling this function, note the following:

  • For the arguments that are keys, you must specify constants.

  • You cannot pass in an ARRAY of keys as the second argument. You must specify each key as a separate argument.

  • The function returns a structured OBJECT. The type of the OBJECT includes the keys in the order in which they are specified.

    For example, suppose that you select the state and city keys in that order:

    SELECT
      OBJECT_PICK(
        {'city':'San Mateo','state':'CA','zip_code':94402}::OBJECT(city VARCHAR,state VARCHAR,zip_code DOUBLE),
        'state',
        'city') AS new_object,
      SYSTEM$TYPEOF(new_object);
    
    Copy

    The function returns an OBJECT of the type OBJECT(state VARCHAR, city VARCHAR).

    +-----------------------+--------------------------------------------------------------+
    | NEW_OBJECT            | SYSTEM$TYPEOF(NEW_OBJECT)                                    |
    |-----------------------+--------------------------------------------------------------|
    | {                     | OBJECT(state VARCHAR(16777216), city VARCHAR(16777216))[LOB] |
    |   "state": "CA",      |                                                              |
    |   "city": "San Mateo" |                                                              |
    | }                     |                                                              |
    +-----------------------+--------------------------------------------------------------+
    

Transforming MAPs

To transform MAPs, use the following functions:

Working With Structured Types

The following sections explain how to use different SQL functions and set operators with objects of structured types:

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:

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:

Passing a structured type as input results in an error.

Accessing Structured Types in Applications Using Drivers

In applications that use drivers (e.g. the ODBC or JDBC driver, the Snowflake Connector for Python, etc.), structured type values are returned as semi-structured types. For example:

  • A structured ARRAY column is returned as a semi-structured ARRAY to the client application.

  • A structured OBJECT or MAP column is returned as a semi-structured OBJECT to the client application.

Note

For client applications that use the JDBC driver, the ResultSet.getArray() method returns an error if the query results you want to retrieve contain a structured ARRAY with NULL values.

To retrieve a string representation instead, use the ResultSet.getString() method:

String result = resultSet.getString(1);
Copy

Using Structured Types With User-Defined Functions (UDFs) and Stored Procedures

When you create a user-defined function (UDF), user-defined table function (UDTF), or stored procedure in SQL, Snowflake Scripting, Java, Python, or Scala, you can use structured types in the arguments and return values. For example:

CREATE OR REPLACE FUNCTION my_udf(
    location OBJECT(city VARCHAR, zipcode NUMBER, val ARRAY(BOOLEAN)))
  RETURNS VARCHAR
  AS
  $$
    ...
  $$;
Copy
CREATE OR REPLACE FUNCTION my_udtf(check BOOLEAN)
  RETURNS TABLE(col1 ARRAY(VARCHAR))
  AS
  $$
  ...
  $$;
Copy
CREATE OR REPLACE PROCEDURE my_procedure(values ARRAY(INTEGER))
  RETURNS ARRAY(INTEGER)
  LANGUAGE SQL
  AS
  $$
    ...
  $$;
Copy
CREATE OR REPLACE FUNCTION my_function(values ARRAY(INTEGER))
  RETURNS ARRAY(INTEGER)
  LANGUAGE PYTHON
  RUNTIME_VERSION=3.8
  AS
  $$
    ...
  $$;
Copy

Note

Structured types are not yet supported in UDFs, UDTFs, and stored procedures in JavaScript.

Viewing Information About Structured Types

The following sections describe the views and commands that you can use to view information about structured types:

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:

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))
Copy

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

ARRAY(NUMBER(38,0))
Copy

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: