SQL JSON extraction and path functions
Query JSON fields in Haybarn SQL, extract plain text, and distinguish missing paths from JSON null values.
Choose a function
Choose the result type first. Use json_extract() to keep a JSON value, json_extract_string() for text, and a subsequent cast for a typed number or date. Use json_exists() when only the presence of a field matters.
json_extract()Keep the extracted value as JSON.json_extract_string()Read an unquoted text value.json_exists()Check whether a path is present.json_type()Inspect the JSON value type.json_valid()Check whether text is valid JSON.
A missing path and a field containing JSON null are different cases. JSON arrays use zero-based positions. Invalid JSON can raise an error before extraction, so validate untrusted text with json_valid().
Extract JSON fields as text
Read a JSON field as VARCHAR without JSON string quotes, ready for text filtering, display, or a subsequent type conversion.
A missing path or a JSON null at the path produces SQL NULL.
Guide example · Run to view results.
| name | price | missing |
|---|---|---|
apple | 12 | NULL |
Watch for: The result is text even when the JSON value is numeric. Cast it before arithmetic, and use TRY_CAST if malformed values should become NULL.
All json functions
56 functions in Haybarn WASM 2.0.0-rc12. Browse the filtered catalog →
array_to_json()from_json()from_json_strict()json()json_array()json_array_length()json_contains()json_copy_strftime_if_date()json_copy_strftime_if_timestamp()json_deep_merge()json_deserialize_sql()json_each()json_execute_serialized_sql()json_exists()Test whether a JSON document contains a path, including paths whose stored value is JSON null.
json_extract()Select a value from a JSON document by path while keeping the result as JSON, including quotes around JSON strings.
json_extract_path()json_extract_path_text()json_extract_string()Read a JSON field as VARCHAR without JSON string quotes, ready for text filtering, display, or a subsequent type conversion.
json_group_array()json_group_object()json_group_structure()json_insert()json_keys()json_merge_patch()json_merge_patch_diff()json_normalize()json_object()json_pretty()json_quote()json_remove()json_replace()json_serialize_plan()json_serialize_sql()json_set()json_strip_nulls()json_structure()json_transform()json_transform_strict()json_tree()json_type()json_valid()json_value()read_json()read_json_auto()read_json_objects()read_json_objects_auto()read_ndjson()read_ndjson_auto()read_ndjson_objects()read_single_json_file()row_to_json()st_asgeojson()st_geomfromgeojson()to_json()__internal_json_copy_to_geojson()__internal_json_copy_to_json()