Skip to content
DuckDB Extensions All extensions JSON Schema

Validate functions in the JSON Schema DuckDB extension

Function category

Validate

2 functions

Conformance checks. json_schema_validate checks a value against a schema; json_schema_validate_schema checks whether a schema can be loaded. Both return TRUE on success and raise on failure. Wrap with COALESCE(TRY(...), false) when a boolean filter is required.

json_schema_validate

Scalar function Validate
Signature
json_schema_validate(schema: JSON, json_data: JSON) → BOOLEAN
Arguments (Positional)
Argument schema Type JSON Mode Positional Description The JSON Schema document to validate against. The validator's primary target is draft-07; newer drafts work for the keywords the underlying pboettch/json-schema-validator supports.
Argument json_data Type JSON Mode Positional Description The JSON value to validate. Pass a column of type JSON, a struct literal, or a string cast with ::JSON.
Returns

BOOLEAN — TRUE for conforming data; raises an error for nonconforming data or an invalid schema. SQL NULL inputs propagate NULL.

Description

Validate a JSON value against a JSON Schema. Validation failures raise an error; they do not return FALSE. For row filtering or valid/invalid counts, use COALESCE(TRY(json_schema_validate(schema, payload)), false). This treats SQL NULL as invalid too. Validate the schema separately first: TRY also catches malformed-schema errors. The function reports a textual exception, not a structured per-keyword report.

1 Validate a single document 
SELECT json_schema_validate('{
  "$schema": "https://json-schema.org/draft-07/schema",
  "type": "object",
  "properties": {
    "id":   {"type": "integer"},
    "name": {"type": "string"}
  },
  "required": ["id"]
}', {'id': 5, 'name': 'George'}) AS ok;
-- TRUE
2 Filter a column on schema conformance 
INSERT INTO events_clean
SELECT * FROM events_raw
WHERE COALESCE(TRY(json_schema_validate(:schema, payload)), false);
3 Aggregate valid / invalid counts 
SELECT
  COUNT(*)                                                         AS total,
  COUNT(*) FILTER (WHERE     COALESCE(TRY(json_schema_validate(:schema, payload)), false)) AS valid,
  COUNT(*) FILTER (WHERE NOT COALESCE(TRY(json_schema_validate(:schema, payload)), false)) AS invalid
FROM events;
Related functions

json_schema_validate_schema

Scalar function Validate
Signature
json_schema_validate_schema(schema: JSON) → BOOLEAN
Arguments (Positional)
Argument schema Type JSON Mode Positional Description Candidate JSON Schema. The function asks the validator to load it; this is not exhaustive validation against the JSON Schema meta-schema.
Returns

BOOLEAN — TRUE if the schema is accepted; raises an error if it is invalid. SQL NULL propagates NULL.

Description

Check that the validator can load a JSON Schema. An invalid schema raises an error, making this useful as a deployment gate. Use COALESCE(TRY(json_schema_validate_schema(definition)), false) when auditing a registry and you want invalid entries flagged instead of aborting the query. Successful loading is not exhaustive meta-schema validation; some unsupported or misspelled keyword values may be accepted.

1 Lint a schema before deploying 
SELECT json_schema_validate_schema('{
  "$schema": "https://json-schema.org/draft-07/schema",
  "type": "object",
  "properties": {
    "id": {"type": "integer"}
  }
}') AS schema_ok;
-- TRUE
2 Find any malformed schemas in a registry table 
SELECT name, version
FROM schema_registry
WHERE NOT COALESCE(TRY(json_schema_validate_schema(definition)), false);
Related functions