A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://docs.snowflake.com/en/sql-reference/data-types-structured.html below:

Website Navigation


Structured data types | Snowflake Documentation

Structured data types

The Snowflake structured types are ARRAY, OBJECT, and MAP. Structured types contain elements or key-value pairs with specific Snowflake data types. The following are examples of structured types:

You can use structured types in the following ways:

This topic explains how to use structured types in Snowflake.

Note

Structured types aren’t supported for dynamic, hybrid, or external tables.

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 type

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

ARRAY( <element_type> [ NOT NULL ] )

Copy

Where:

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

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 type

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

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

Copy

Where:

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

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

Copy

Where:

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;

Copy

+----------------------------+
| MAP_EXAMPLE                |
|----------------------------|
| MAP(VARCHAR, VARCHAR)[LOB] |
+----------------------------+
Creating a table with a structured type column

When you use the CREATE TABLE command to create a table, you can use the syntax described in Specifying a structured type to define a column that contains a structured type.

The following examples demonstrate how to specify a structured type column:

Example of creating a table with a structured ARRAY column

The following statement creates a table with a column for a structured ARRAY:

CREATE TABLE my_table_with_structured_array_column (
  numeric_array ARRAY(NUMBER)
);

Copy

The following statement inserts a row into the table:

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

Copy

Note the following:

Example of creating a table with a structured OBJECT column

The following statement creates a table with a column for a structured OBJECT:

CREATE TABLE customer (
  c_id VARCHAR,
  c_name VARCHAR,
  c_address OBJECT(
    state VARCHAR,
    city VARCHAR,
    street VARCHAR,
    zip_code NUMBER
  )
);

Copy

The following statement inserts a row into the table:

INSERT INTO customer SELECT
  '1',
  'customer_name',
  {
    'state': 'CA',
    'city': 'San Mateo',
    'street': '450 Concar Drive',
    'zip_code': 94402
  }::OBJECT(
    state VARCHAR,
    city VARCHAR,
    street VARCHAR,
    zip_code NUMBER
  );

Copy

Note the following:

Example of creating a table with a MAP column

The following statement creates a table with a column for a MAP:

CREATE OR REPLACE TABLE my_table_with_map_column(my_map MAP(VARCHAR, VARCHAR));

Copy

The following statement inserts a row into the table:

INSERT INTO my_table_with_map_column SELECT
  {'key123': 'value123'}::MAP(VARCHAR, VARCHAR);

Copy

Note the following:

Adding a structured type column

To add a column containing a structured type, use ALTER TABLE … ADD COLUMN with the syntax described in Specifying a structured type. For example:

ALTER TABLE customer ADD COLUMN phone ARRAY(VARCHAR);

Copy

Dropping and renaming structured type columns

To drop or rename a structured type column, you can use ALTER TABLE … DROP COLUMN and ALTER TABLE … RENAME COLUMN (as you would with a column with a semi-structured object).

Using structured types in semi-structured types

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

Converting structured and semi-structured types

The following table summarizes rules for converting structured OBJECT, structured ARRAY, and MAP values to semi-structured OBJECT, ARRAY, and VARIANT values (and vice versa).

Source data type

Target data type

Castable

Coercible

Semi-structured ARRAY

Structured ARRAY

✔

❌

Semi-structured OBJECT

✔

❌

Semi-structured VARIANT

✔

❌

Structured ARRAY

Semi-structured ARRAY

✔

❌

Semi-structured OBJECT

✔

❌

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 value of a semi-structured type to a value of a structured type, you can call the CAST function or use the :: operator.

Note

TRY_CAST isn’t supported for structured types.

You can only cast values of the following semi-structured types to values of 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 ARRAY and VARIANT values to structured ARRAY values

The following steps demonstrate how to cast a semi-structured ARRAY or VARIANT value to an ARRAY value 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 value to a structured ARRAY value, note the following:

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

The following steps demonstrate how to cast a semi-structured OBJECT or VARIANT value to a structured OBJECT value 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, state VARCHAR)[LOB] | OBJECT(city VARCHAR, state VARCHAR)[LOB] |
+------------------------------------------+------------------------------------------+

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

Casting semi-structured OBJECT and VARIANT values to MAP values

The following statements demonstrate how to cast a semi-structured OBJECT or VARIANT value to a MAP value 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, VARCHAR)[LOB] | MAP(VARCHAR, VARCHAR)[LOB] |
+----------------------------+----------------------------+

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

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

Note

TRY_CAST isn’t 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:

Note the following:

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:

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:

Note

TRY_CAST isn’t supported with structured types.

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.

Example: Casting from one type of ARRAY value to another

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

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

Copy

+------------+
| CAST_ARRAY |
|------------|
| [          |
|   "1",     |
|   "2",     |
|   "3"      |
| ]          |
+------------+
Example: Changing the order of key-value pairs in an OBJECT value

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

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

Copy

+-----------------------+
| OBJECT_VALUE_ORDER    |
|-----------------------|
| {                     |
|   "state": "CA",      |
|   "city": "San Mateo" |
| }                     |
+-----------------------+
Example: Changing the key names in an OBJECT value

To change the key names in a structured OBJECT value, 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) AS object_value_key_names;

Copy

+-----------------------------+
| OBJECT_VALUE_KEY_NAMES      |
|-----------------------------|
| {                           |
|   "city_name": "San Mateo", |
|   "state_name": "CA"        |
| }                           |
+-----------------------------+
Example: Adding keys to an OBJECT value

If the type that you are casting to has additional key-value pairs that aren’t present in the original structured OBJECT value, 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) AS add_fields;

Copy

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

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

Constructing structured ARRAY, structured OBJECT, and MAP values

The following sections explain how to construct structured ARRAY, structured OBJECT, and MAP values.

Using SQL functions to construct structured ARRAY and OBJECT values

The following functions construct semi-structured ARRAY values:

The following functions construct semi-structured OBJECT values:

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

Copy

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

Copy

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

Note

You can’t pass structured ARRAY, structured OBJECT, or MAP values to these functions. Doing so would result in a structured type being implicitly cast to a semi-structured type, which isn’t allowed, as noted in Implicit casting a value (coercion).

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

Copy

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

Copy

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

Copy

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

Copy

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

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

Copy

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

Working with keys, values, and elements in values of structured types

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

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

Copy

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

Copy

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

Copy

+-------------------------------+--------------------------+
| SEMI_STRUCTURED_ARRAY_ELEMENT | STRUCTURED_ARRAY_ELEMENT |
|-------------------------------+--------------------------|
| VARIANT[LOB]                  | VARCHAR[LOB]             |
+-------------------------------+--------------------------+

Note the following:

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

Copy

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

Copy

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

Copy

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

Copy

Note

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

Don’t cast the expression for the element to a VARIANT value.

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

Copy

SELECT MAP_CONTAINS_KEY(10, my_map);

Copy

Comparing values

The following sections explain how to compare values:

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:

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)

Copy

is greater than:

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

Copy

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

Copy

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 values of structured types

The following sections explain how to transform structured ARRAY, structured OBJECT, and MAP values:

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:

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

Functions that add elements to ARRAY values

The following functions add elements to an ARRAY values:

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

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

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

Copy

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

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

Copy

The following call fails because DATE values can’t be coerced to NUMBER values:

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

Copy

Functions that accept multiple ARRAY values as input

The following functions accept multiple ARRAY values as input arguments:

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

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 value of the same type as the ARRAY value in the first argument.

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

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

The ARRAY value 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 value in the second argument must be coercible to the type in the first argument.

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

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

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

Copy

The following call fails because an ARRAY(NUMBER) value isn’t comparable to an ARRAY(VARCHAR) value:

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

Copy

Transforming structured OBJECT values

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

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 value that contains the key-value pairs from an existing OBJECT value with specific key-value pairs removed, call the OBJECT_DELETE function.

When calling this function, note the following:

Inserting key-value pairs and updating values

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

When calling this function, note the following:

Selecting key-value pairs from an existing OBJECT

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

When calling this function, note the following:

Transforming MAP values

To transform MAP values, use the following functions:

Working with structured types

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

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.

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:

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:

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

Accessing structured types in applications using drivers

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

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 value 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.10
  AS
  $$
    ...
  $$;

Copy

Note

Structured types aren’t 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:

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

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:

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4