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

See also

• JSON functions

Examples

Edge cases

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