Extract values from a JSON document ​

Use the JSON functions that match the desired result type. A structured extraction retains a JSON value, while a text extraction returns decoded text. JSON null and SQL NULL have different meanings, so include both in tests.

SELECT json_extract_path_text('{"user":{"name":"Ada"}}', 'user', 'name') AS name,
       json_extract('{"items":[10,20]}', '$.items[1]') AS second_item
FORMAT csv;

Choose literal components or a path ​

json_extract_path_text accepts literal object keys and signed zero-based array indexes. json_extract accepts the documented JSON path syntax. Avoid building either expression by concatenating untrusted SQL text. In an application, encode input as data using a supported ingestion format or safely quote values according to the SQL contract.

For fields with a stable schema, a typed JSON path declaration can enforce a native type at write time and expose it through dotted column access. Missing typed paths receive the type default unless the declared type is Nullable.

Validate the boundary cases ​

Check missing keys, explicit JSON null, empty arrays and a scalar where an object is expected. A query returning an empty string or zero can otherwise hide the distinction between a real value and a chosen default.

See JSON functions and typed JSON paths.