IS_VALID_JSON

Return true if the input string is a well-formed JSON value.

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

IS_VALID_JSON(str)

Description

## Overview Returns TRUE if the input string is a well-formed JSON value as defined by RFC 8259, FALSE otherwise, and NULL for a NULL input. Use this function to validate raw payloads at ingest, to filter quarantine rows before running JSON extraction functions, or to guard an expensive JSON parse. ## Validation rules - Any valid JSON value is accepted: object, array, string, number, boolean, or null. - Keys must be double-quoted strings. - Strings use double quotes and standard JSON escape sequences. - Trailing commas in objects and arrays are rejected. - Comments are rejected. - Leading and trailing whitespace (including newlines) is allowed and does not affect the result. - UTF-8 encoding is assumed. ## Behavior - Returns NULL for NULL input; otherwise always TRUE or FALSE. - Deterministic and side effect free. - Does not materialize or retain the parsed structure; the cost is proportional to the input length. ## Compatibility - Matches RFC 8259 / ECMA-404 JSON syntax. Looser variants such as JSON5 are not accepted.

Parameters

Name	Type	Description
`str`		Specifies the string to validate as JSON. Any JSON value (object, array, string, number, boolean, or null) is accepted.

Examples

-- Valid JSON object
SELECT IS_VALID_JSON('{"key": "value"}');  -- true

-- Valid JSON array
SELECT IS_VALID_JSON('[1, 2, 3]');  -- true

-- Bare JSON number (valid RFC 8259 JSON)
SELECT IS_VALID_JSON('42');  -- true

-- Invalid JSON (unquoted keys)
SELECT IS_VALID_JSON('{key: value}');  -- false

-- Filter malformed payloads during ingest
SELECT event_id, raw_payload
FROM obs.stage.events
WHERE NOT IS_VALID_JSON(raw_payload);

-- Guard JSON extraction
SELECT CASE WHEN IS_VALID_JSON(body) THEN json_extract_path(body, 'user', 'id') END AS user_id
FROM obs.stage.events;

Pitfalls

IS_VALID_JSON accepts bare scalars (`42`, `true`, `"s"`) because they are valid JSON values per RFC 8259. If you only want to accept objects or arrays, filter with `json_typeof` after validation.
Trailing commas and unquoted keys are rejected even though some permissive parsers accept them. Normalize the input upstream or use a JSON5 preprocessor if needed.
Large payloads still pay a full parse cost. For very wide rows where most payloads are valid, consider sampling during quarantine checks rather than validating every row.
A NULL input returns NULL, not FALSE. `WHERE NOT IS_VALID_JSON(col)` excludes rows where `col` is NULL; add an explicit check if you want to include them.