JSON Functions

This section provides reference information for JSON functions in Databend. JSON functions enable parsing, validation, querying, and manipulation of JSON data structures.

JSON Parsing & Validation

Function	Description	Example
PARSE_JSON	Parses a JSON string into a variant value	PARSE_JSON('{"name":"John","age":30}') → {"name":"John","age":30}
CHECK_JSON	Validates if a string is valid JSON	CHECK_JSON('{"valid": true}') → true

JSON Type Information

Function	Description	Example
JSON_TYPEOF	Returns the type of a JSON value	JSON_TYPEOF('{"key": "value"}') → 'OBJECT'

JSON Conversion

Function	Description	Example
JSON_TO_STRING	Converts a JSON value to a string	JSON_TO_STRING({"name":"John"}) → '{"name":"John"}'

JSON Path Operations

Function	Description	Example
JSON_PATH_EXISTS	Checks if a JSON path exists	JSON_PATH_EXISTS('{"a":1}', '$.a') → true
JSON_PATH_MATCH	Matches JSON values against a path pattern	JSON_PATH_MATCH('{"items":[1,2,3]}', '$.items[*]') → [1,2,3]
JSON_PATH_QUERY	Queries JSON data using JSONPath	JSON_PATH_QUERY('{"a":1,"b":2}', '$.a') → 1
JSON_PATH_QUERY_ARRAY	Queries JSON data and returns results as an array	JSON_PATH_QUERY_ARRAY('[1,2,3]', '$[*]') → [1,2,3]
JSON_PATH_QUERY_FIRST	Returns the first result from a JSON path query	JSON_PATH_QUERY_FIRST('[1,2,3]', '$[*]') → 1

JSON Data Extraction

Function	Description	Example
GET	Extracts value from JSON by index or field name	GET('{"name":"John"}', 'name') → "John"
GET_IGNORE_CASE	Extracts value with case-insensitive field matching	GET_IGNORE_CASE('{"Name":"John"}', 'name') → "John"
GET_BY_KEYPATH	Extracts nested value using brace key paths	GET_BY_KEYPATH('{"user":{"name":"Ada"}}', '{user,name}') → "Ada"
GET_PATH	Extracts value using path notation	GET_PATH('{"user":{"name":"John"}}', 'user.name') → "John"
JSON_EXTRACT_PATH_TEXT	Extracts text value from JSON using path	JSON_EXTRACT_PATH_TEXT('{"name":"John"}', 'name') → 'John'
JSON_EACH	Expands JSON object into key-value pairs	JSON_EACH('{"a":1,"b":2}') → [("a",1),("b",2)]
JSON_ARRAY_ELEMENTS	Expands JSON array into individual elements	JSON_ARRAY_ELEMENTS('[1,2,3]') → 1, 2, 3
JQ	Processes JSON using jq-style queries	JQ('{"name":"John"}', '.name') → "John"

JSON Formatting & Processing

Function	Description	Example
JSON_PRETTY	Formats JSON with proper indentation	JSON_PRETTY('{"a":1}') → Formatted JSON string
STRIP_NULL_VALUE	Converts a JSON null value to a SQL NULL value	STRIP_NULL_VALUE(parse_json('null')) → NULL
JSON_STRIP_NULLS	Removes null values from JSON Object	JSON_STRIP_NULLS(PARSE_JSON('{"a":1,"b":null}')) → {"a":1}

JSON Containment & Existence

Function	Description	Example
JSON_CONTAINS_IN_LEFT	Tests whether the left JSON contains the right JSON	JSON_CONTAINS_IN_LEFT('{"a":1,"b":2}', '{"b":2}') → true
JSON_EXISTS_KEY	Checks whether specific keys exist	JSON_EXISTS_KEY('{"a":1}', 'a') → true
JSON_EXISTS_ANY_KEYS	Returns true if any key in the list exists	JSON_EXISTS_ANY_KEYS('{"a":1}', ['x','a']) → true
JSON_EXISTS_ALL_KEYS	Returns true only if all keys exist	JSON_EXISTS_ALL_KEYS('{"a":1,"b":2}', ['a','b']) → true