JSON Functions This section provides reference information for JSON functions in TiDB Cloud Lake. 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
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') → trueJSON_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') → 1JSON_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
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, 3JQ Processes JSON using jq-style queries JQ('{"name":"John"}', '.name') → "John"
Function Description Example JSON_PRETTY Formats JSON with proper indentation JSON_PRETTY('{"a":1}') → Formatted JSON stringSTRIP_NULL_VALUE Removes null values from JSON STRIP_NULL_VALUE('{"a":1,"b":null}') → {"a":1}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}') → trueJSON_EXISTS_KEY Checks whether specific keys exist JSON_EXISTS_KEY('{"a":1}', 'a') → trueJSON_EXISTS_ANY_KEYS Returns true if any key in the list exists JSON_EXISTS_ANY_KEYS('{"a":1}', ['x','a']) → trueJSON_EXISTS_ALL_KEYS Returns true only if all keys exist JSON_EXISTS_ALL_KEYS('{"a":1,"b":2}', ['a','b']) → true