Semi-structured and structured data functions

These functions are used with:

List of semi-structured and structured data functions

The functions are grouped by type of operation performed:

  • Parsing JSON and XML data.

  • Creating and manipulating ARRAYs and OBJECTs.

  • Extracting values from semi-structured and structured data (e.g. from an ARRAY, OBJECT, or MAP).

  • Converting/casting semi-structured data types and structured data types to/from other data types.

  • Determining the data type for values in semi-structured data (i.e. type predicates).

Sub-category

Function

Notes

JSON and XML Parsing

CHECK_JSON

CHECK_XML

Preview feature.

JSON_EXTRACT_PATH_TEXT

PARSE_JSON

PARSE_XML

Preview feature.

STRIP_NULL_VALUE

Array/Object Creation and Manipulation

ARRAY_AGG

See also Aggregate Functions.

ARRAY_APPEND

ARRAY_CAT

ARRAY_COMPACT

ARRAY_CONSTRUCT

ARRAY_CONSTRUCT_COMPACT

ARRAY_CONTAINS

ARRAY_DISTINCT

ARRAY_EXCEPT

ARRAY_FLATTEN

ARRAY_GENERATE_RANGE

ARRAY_INSERT

ARRAY_INTERSECTION

ARRAY_MAX

ARRAY_MIN

ARRAY_POSITION

ARRAY_PREPEND

ARRAY_REMOVE

ARRAY_REMOVE_AT

ARRAY_SIZE

ARRAY_SLICE

ARRAY_SORT

ARRAY_TO_STRING

ARRAY_UNION_AGG

See also Aggregate Functions.

ARRAY_UNIQUE_AGG

See also Aggregate Functions.

ARRAYS_OVERLAP

ARRAYS_TO_OBJECT

OBJECT_AGG

See also Aggregate Functions.

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT_KEEP_NULL

OBJECT_DELETE

OBJECT_INSERT

OBJECT_PICK

Higher-order

FILTER

See also Filter and transform data with Snowflake higher-order functions.

TRANSFORM

See also Filter and transform data with Snowflake higher-order functions.

Map Creation and Manipulation

MAP_CAT

Preview feature.

MAP_CONTAINS_KEY

Preview feature.

MAP_DELETE

Preview feature.

MAP_INSERT

Preview feature.

MAP_KEYS

Preview feature.

MAP_PICK

Preview feature.

MAP_SIZE

Preview feature.

Extraction

FLATTEN

Table function.

GET

GET_IGNORE_CASE

GET_PATH , :

Variation of GET.

OBJECT_KEYS

Extracts keys from key/value pairs in OBJECT.

XMLGET

Preview feature.

Conversion/Casting

AS_<object_type>

AS_ARRAY

AS_BINARY

AS_CHAR , AS_VARCHAR

AS_DATE

AS_DECIMAL , AS_NUMBER

AS_DOUBLE , AS_REAL

AS_INTEGER

AS_OBJECT

AS_TIME

AS_TIMESTAMP_*

STRTOK_TO_ARRAY

TO_ARRAY

TO_JSON

TO_OBJECT

TO_VARIANT

TO_XML

Type Predicates

IS_<object_type>

IS_ARRAY

IS_BOOLEAN

IS_BINARY

IS_CHAR , IS_VARCHAR

IS_DATE , IS_DATE_VALUE

IS_DECIMAL

IS_DOUBLE , IS_REAL

IS_INTEGER

IS_NULL_VALUE

IS_OBJECT

IS_TIME

IS_TIMESTAMP_*

TYPEOF