JSON_EXTRACT_SCALAR

All functions > JSON > JSON_EXTRACT_SCALAR

Returns the scalar value of the given JSON path in the given JSON expression.

Signatures

Returns: The scalar value as VARCHAR at the specified path

JSON_EXTRACT_SCALAR(expr: JSON, json_path: VARCHAR) → VARCHAR

Notes

• Extracts scalar values from JSON as strings
• Converts JSON primitives (strings, numbers, booleans) to VARCHAR
• Supports nested field access with dot notation
• Supports array indexing ($.array[0])
• Returns NULL if the path doesn't exist
• Returns NULL if the value is not a scalar (object or array)
• Use JSON_EXTRACT to preserve JSON structure
• JSONPath starts with $ representing the root

Examples

SELECT
    f1 := JSON_EXTRACT_SCALAR(JSON_PARSE('{"name": "Alice", "age": 30}'), '$.name'),  -- Extract string value
    f2 := JSON_EXTRACT_SCALAR(JSON_PARSE('{"name": "Alice", "age": 30}'), '$.age'),  -- Extract number as string
    f3 := JSON_EXTRACT_SCALAR(JSON_PARSE('{"active": true}'), '$.active')  -- Extract boolean as string
;