Semi-structured Data Functions

These functions are used with semi-structured data (JSON, Avro, XML), typically stored in Snowflake in VARIANT, OBJECT, or ARRAY columns.

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

  • Converting/casting semi-structured data 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.

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_INSERT

ARRAY_POSITION

ARRAY_PREPEND

ARRAY_SIZE

ARRAY_SLICE

ARRAY_TO_STRING

ARRAYS_OVERLAP

OBJECT_AGG

See also Aggregate Functions.

OBJECT_CONSTRUCT

OBJECT_DELETE

OBJECT_INSERT

Extraction

FLATTEN

Table function.

GET

GET_IGNORE_CASE

GET_PATH , :

Variation of GET.

XMLGET

Preview feature.

Conversion/Casting

AS_<object_type>

STRTOK_TO_ARRAY

TO_ARRAY

TO_ARRAY

TO_JSON

TO_OBJECT

TO_VARIANT

TO_XML

Type Predicates

IS_<object_type>

TYPEOF