Semi-Structured Functions

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

Basic Operations

Parsing and Validation

• PARSE_JSON: Parses a JSON string into a variant value
• CHECK_JSON: Validates if a string is valid JSON

Object Access and Extraction

• GET: Gets a value from a JSON object by key
• GET_PATH: Gets a value from a JSON object by path
• GET_IGNORE_CASE: Gets a value with case-insensitive key matching
• OBJECT_KEYS: Returns keys of a JSON object
• JSON_OBJECT_KEYS: Returns keys of a JSON object as an array

Type Inspection and Conversion

• JSON_TYPEOF: Returns the type of a JSON value
• AS_TYPE: Converts a JSON value to a specified SQL type
• JSON_TO_STRING: Converts a JSON value to a string
• IS_OBJECT: Checks if a JSON value is an object
• IS_ARRAY: Checks if a JSON value is an array
• IS_STRING: Checks if a JSON value is a string
• IS_INTEGER: Checks if a JSON value is an integer
• IS_FLOAT: Checks if a JSON value is a floating-point number
• IS_BOOLEAN: Checks if a JSON value is a boolean
• IS_NULL_VALUE: Checks if a JSON value is null

Construction and Modification

JSON Object Operations

• JSON_OBJECT: Creates a JSON object from key-value pairs
• JSON_OBJECT_INSERT: Inserts a value into a JSON object
• JSON_OBJECT_DELETE: Deletes a key from a JSON object
• JSON_OBJECT_PICK: Creates a new object with selected keys
• JSON_STRIP_NULLS: Removes null values from a JSON object
• JSON_OBJECT_KEEP_NULL: Creates a JSON object preserving null values

JSON Array Operations

• JSON_ARRAY: Creates a JSON array from input values
• JSON_ARRAY_INSERT: Inserts a value into a JSON array
• JSON_ARRAY_DISTINCT: Returns an array with distinct elements
• FLATTEN: Flattens nested arrays into a single array

Advanced Query and Transformation

Path Queries

• JSON_PATH_EXISTS: Checks if a JSON path exists
• JSON_PATH_QUERY: Queries JSON data using a path expression
• JSON_PATH_QUERY_FIRST: Returns the first match from a path query
• JSON_PATH_QUERY_ARRAY: Returns query results as a JSON array
• JSON_EXTRACT_PATH_TEXT: Extracts text from a JSON path
• JSON_PATH_MATCH: Matches JSON data against a path expression
• JQ: Provides jq-like JSON processing capabilities

Array Transformations

• JSON_ARRAY_MAP: Maps a function over array elements
• JSON_ARRAY_FILTER: Filters array elements using a condition
• JSON_ARRAY_TRANSFORM: Transforms array elements using an expression
• JSON_ARRAY_APPLY: Applies a function to each array element
• JSON_ARRAY_REDUCE: Reduces an array to a single value

Set Operations

• JSON_ARRAY_INTERSECTION: Returns common elements between arrays
• JSON_ARRAY_EXCEPT: Returns elements in first array but not in second
• JSON_ARRAY_OVERLAP: Checks if arrays have common elements

Object Transformations

• JSON_MAP_FILTER: Filters key-value pairs in a JSON object
• JSON_MAP_TRANSFORM_KEYS: Transforms keys in a JSON object
• JSON_MAP_TRANSFORM_VALUES: Transforms values in a JSON object

Expansion and Formatting

• JSON_ARRAY_ELEMENTS: Expands a JSON array to a set of rows
• JSON_EACH: Expands the outermost JSON object into key-value pairs
• JSON_PRETTY: Formats JSON with indentation for readability