Semi-Structured Functions

This section provides reference information for the semi-structured data functions in PlaidCloud Lakehouse.

JSON Parsing, Conversion & Type Checking:

JSON Query and Extraction:

JSON Data Manipulation:

Object Operations:

Type Conversion:

AS_<type>

Learn how to use the AS_ semi-structured data function in PlaidCloud Lakehouse. Strict casting VARIANT values to other data types. Includes syntax and...

CHECK_JSON

Learn how to use the CHECK_JSON semi-structured data function in PlaidCloud Lakehouse. Checks the validity of a JSON document. Includes syntax and examples.

FLATTEN

Learn how to use the FLATTEN semi-structured data function in PlaidCloud Lakehouse. Transforms nested JSON data into a tabular format, where each element or...

GET

Learn how to use the GET semi-structured data function in PlaidCloud Lakehouse. Extracts value from a Variant that contains ARRAY by index, or a Variant...

GET_IGNORE_CASE

Learn how to use the GET_IGNORE_CASE semi-structured data function in PlaidCloud Lakehouse. Extracts value from a VARIANT that contains OBJECT by the...

GET_PATH

Learn how to use the GET_PATH semi-structured data function in PlaidCloud Lakehouse. Extracts value from a VARIANT by path_name. Includes syntax and examples.

IS_ARRAY

Learn how to use the IS_ARRAY semi-structured data function in PlaidCloud Lakehouse. Checks if the input value is a JSON array. Includes syntax and examples.

IS_BOOLEAN

Learn how to use the IS_BOOLEAN semi-structured data function in PlaidCloud Lakehouse. Checks if the input JSON value is a boolean. With syntax and examples.

IS_FLOAT

Learn how to use the IS_FLOAT semi-structured data function in PlaidCloud Lakehouse. Checks if the input JSON value is a float. Includes syntax and examples.

IS_INTEGER

Learn how to use the IS_INTEGER semi-structured data function in PlaidCloud Lakehouse. Checks if the input JSON value is an integer. With syntax and examples.

IS_NULL_VALUE

Learn how to use the IS_NULL_VALUE semi-structured data function in PlaidCloud Lakehouse. Checks whether the input value is a JSON null. Includes syntax and...

IS_OBJECT

Learn how to use the IS_OBJECT semi-structured data function in PlaidCloud Lakehouse. Checks if the input value is a JSON object. Includes syntax and examples.

IS_STRING

Learn how to use the IS_STRING semi-structured data function in PlaidCloud Lakehouse. Checks if the input JSON value is a string. Includes syntax and examples.

JQ

Learn how to use the JQ semi-structured data function in PlaidCloud Lakehouse. The JQ function is a set-returning SQL function that allows you to apply jq...

JSON_ARRAY

Learn how to use the JSON_ARRAY semi-structured data function in PlaidCloud Lakehouse. Creates a JSON array with specified values. Includes syntax and examples.

JSON_ARRAY_APPLY

Learn how to use JSON_ARRAY_APPLY in PlaidCloud Lakehouse. Alias for the JSON_ARRAY_TRANSFORM semi-structured data function. Includes syntax and examples.

JSON_ARRAY_DISTINCT

Learn how to use the JSON_ARRAY_DISTINCT semi-structured data function in PlaidCloud Lakehouse. Removes duplicate elements from a JSON array and returns an...

JSON_ARRAY_ELEMENTS

Learn how to use the JSON_ARRAY_ELEMENTS semi-structured data function in PlaidCloud Lakehouse. Extracts the elements from a JSON array, returning them as...

JSON_ARRAY_EXCEPT

Learn how to use the JSON_ARRAY_EXCEPT semi-structured data function in PlaidCloud Lakehouse. Returns a new JSON array containing the elements from the...

JSON_ARRAY_FILTER

Learn how to use the JSON_ARRAY_FILTER semi-structured data function in PlaidCloud Lakehouse. Filters elements from a JSON array based on a specified Lambda...

JSON_ARRAY_INSERT

Learn how to use the JSON_ARRAY_INSERT semi-structured data function in PlaidCloud Lakehouse. Inserts a value into a JSON array at the specified index and...

JSON_ARRAY_INTERSECTION

Learn how to use the JSON_ARRAY_INTERSECTION semi-structured data function in PlaidCloud Lakehouse. Returns the common elements between two JSON arrays.

JSON_ARRAY_MAP

Learn how to use JSON_ARRAY_MAP in PlaidCloud Lakehouse. Alias for the JSON_ARRAY_TRANSFORM semi-structured data function. Includes syntax and examples.

JSON_ARRAY_OVERLAP

Learn how to use the JSON_ARRAY_OVERLAP semi-structured data function in PlaidCloud Lakehouse. Checks if there is any overlap between two JSON arrays and...

JSON_ARRAY_REDUCE

Learn how to use the JSON_ARRAY_REDUCE semi-structured data function in PlaidCloud Lakehouse. Reduces a JSON array to a single value by applying a specified...

JSON_ARRAY_TRANSFORM

Learn how to use the JSON_ARRAY_TRANSFORM semi-structured data function in PlaidCloud Lakehouse. Transforms each element of a JSON array using a specified...

JSON_EACH

Learn how to use the JSON_EACH semi-structured data function in PlaidCloud Lakehouse. Extracts key-value pairs from a JSON object, breaking down the...

JSON_EXTRACT_PATH_TEXT

Learn how to use the JSON_EXTRACT_PATH_TEXT semi-structured data function in PlaidCloud Lakehouse. Extracts value from a Json string by path_name.

JSON_MAP_FILTER

Learn how to use the JSON_MAP_FILTER semi-structured data function in PlaidCloud Lakehouse. Filters key-value pairs in a JSON object based on a specified...

JSON_MAP_TRANSFORM_KEYS

Learn how to use the JSON_MAP_TRANSFORM_KEYS semi-structured data function in PlaidCloud Lakehouse. Applies a transformation to each key in a JSON object...

JSON_MAP_TRANSFORM_VALUES

Learn how to use the JSON_MAP_TRANSFORM_VALUES semi-structured data function in PlaidCloud Lakehouse. Applies a transformation to each value in a JSON...

JSON_OBJECT_DELETE

Learn how to use the JSON_OBJECT_DELETE semi-structured data function in PlaidCloud Lakehouse. Deletes specified keys from a JSON object and returns the...

JSON_OBJECT_INSERT

Learn how to use the JSON_OBJECT_INSERT semi-structured data function in PlaidCloud Lakehouse. Inserts or updates a key-value pair in a JSON object.

JSON_OBJECT_KEEP_NULL

Learn how to use the JSON_OBJECT_KEEP_NULL semi-structured data function in PlaidCloud Lakehouse. Creates a JSON object with keys and values.

JSON_OBJECT_KEYS

Learn how to use the JSON_OBJECT_KEYS semi-structured data function in PlaidCloud Lakehouse. Returns an Array containing the list of keys in the input...

JSON_OBJECT_PICK

Learn how to use the JSON_OBJECT_PICK semi-structured data function in PlaidCloud Lakehouse. Creates a new JSON object containing only the specified keys...

JSON_PATH_EXISTS

Learn how to use the JSON_PATH_EXISTS semi-structured data function in PlaidCloud Lakehouse. Checks whether a specified path exists in JSON data.

JSON_PATH_MATCH

Learn how to use the JSON_PATH_MATCH semi-structured data function in PlaidCloud Lakehouse. Checks whether a specified JSON path expression matches certain...

JSON_PATH_QUERY

Learn how to use the JSON_PATH_QUERY semi-structured data function in PlaidCloud Lakehouse. Get all JSON items returned by JSON path for the specified JSON...

JSON_PATH_QUERY_ARRAY

Learn how to use the JSON_PATH_QUERY_ARRAY semi-structured data function in PlaidCloud Lakehouse. Get all JSON items returned by JSON path for the specified...

JSON_PATH_QUERY_FIRST

Learn how to use the JSON_PATH_QUERY_FIRST semi-structured data function in PlaidCloud Lakehouse. Get the first JSON item returned by JSON path for the...

JSON_PRETTY

Learn how to use the JSON_PRETTY semi-structured data function in PlaidCloud Lakehouse. Formats JSON data, making it more readable and presentable.

JSON_STRIP_NULLS

Learn how to use the JSON_STRIP_NULLS semi-structured data function in PlaidCloud Lakehouse. Removes all properties with null values from a JSON object.

JSON_TO_STRING

Learn how to use JSON_TO_STRING in PlaidCloud Lakehouse. Alias for the TO_STRING semi-structured data function. Includes syntax and examples.

JSON_TYPEOF

Learn how to use the JSON_TYPEOF semi-structured data function in PlaidCloud Lakehouse. Returns the type of the main-level of a JSON structure.

OBJECT_KEYS

Learn how to use OBJECT_KEYS in PlaidCloud Lakehouse. Alias for the JSON_OBJECT_KEYS semi-structured data function. Includes syntax and examples.

PARSE_JSON

Interprets input JSON string, producing a VARIANT value