Semi-structured Data Functions¶

These functions are used with:

• semi-structured data formats (including JSON, Avro, and XML)

• semi-structured data types (including VARIANT, OBJECT, and ARRAY).

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 (e.g. from an ARRAY or OBJECT).

• Converting/casting semi-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_GENERATE_RANGE
	ARRAY_INSERT
	ARRAY_INTERSECTION
	ARRAY_POSITION
	ARRAY_PREPEND
	ARRAY_REMOVE
	ARRAY_REMOVE_AT
	ARRAY_SIZE
	ARRAY_SLICE
	ARRAY_TO_STRING
	ARRAYS_OVERLAP
	OBJECT_AGG	See also Aggregate Functions.
	OBJECT_CONSTRUCT
	OBJECT_CONSTRUCT_KEEP_NULL
	OBJECT_DELETE
	OBJECT_INSERT
	OBJECT_PICK
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