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')
;