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.
  • Creating and manipulating 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  
PARSE_JSON  
PARSE_XML  
STRIP_NULL_VALUE  
Array Creation/Manipulation ARRAY_AGG  
ARRAY_APPEND  
ARRAY_CAT  
ARRAY_COMPACT  
ARRAY_CONSTRUCT  
ARRAY_CONSTRUCT_COMPACT  
ARRAY_INSERT  
ARRAY_PREPEND  
ARRAY_SIZE  
ARRAY_SLICE  
ARRAY_TO_STRING  
Object Creation/Manipulation OBJECT_AGG  
OBJECT_CONSTRUCT  
OBJECT_DELETE  
OBJECT_INSERT  
Extraction FLATTEN Table function.
GET  
GET_PATH , :  
XMLGET  
Conversion/Casting AS_type  
 
 
 
 
 
 
 
 
 
 
TO_ARRAY  
TO_JSON  
TO_OBJECT  
TO_VARIANT  
TO_XML  
Type Predicates IS_type  
 
 
 
 
 
 
 
 
 
 
 
 
TYPEOF