JSON_EXTRACT

All functions > JSON > JSON_EXTRACT

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

Signatures

Returns: The JSON value at the specified path

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

Notes

• Extracts values from JSON documents using JSONPath syntax
• Supports nested field access with dot notation ($.field.subfield)
• Supports array indexing ($.array[0])
• Returns JSON type, preserving structure (objects, arrays, etc.)
• Returns NULL if the path doesn't exist
• Use JSON_EXTRACT_SCALAR for string values
• JSONPath starts with $ representing the root

Examples

SELECT
    f1 := JSON_EXTRACT(JSON_PARSE('{"name": "Alice", "age": 30}'), '$.name'),  -- Extract field from JSON object
    f2 := JSON_EXTRACT(JSON_PARSE('{"users": [{"name": "Bob"}, {"name": "Charlie"}]}'), '$.users[0]'),  -- Extract array element
    f3 := JSON_EXTRACT(JSON_PARSE('{"data": {"value": 42}}'), '$.data.value')  -- Extract nested field
;