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`](#json_schema_validate) checks a value against a schema; [`json_schema_validate_schema`](#json_schema_validate_schema) checks the schema itself. Both return `BOOLEAN` — drop into `WHERE` clauses, `FILTER` aggregates, and CI lint queries.

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 if the value conforms, FALSE otherwise.

Description

Validate a JSON value against a JSON Schema. Returns a plain BOOLEAN, so it composes naturally with WHERE, CASE, and COUNT(*) FILTER (...).

This function does not return structured error detail — it answers conformance only. For a full per-keyword failure report, validate at the application layer with ajv / Pydantic / jsonschema and use this function as the coarse SQL gate.

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 json_schema_validate(:schema, payload);
3 Aggregate valid / invalid counts 
SELECT
  COUNT(*)                                                         AS total,
  COUNT(*) FILTER (WHERE     json_schema_validate(:schema, payload)) AS valid,
  COUNT(*) FILTER (WHERE NOT json_schema_validate(:schema, payload)) 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 The candidate JSON Schema document. Will be checked against the validator's meta-schema.
Returns

BOOLEAN — TRUE if the schema is well-formed, FALSE otherwise.

Description

Lint a JSON Schema before applying it. Catches typos and structural mistakes that would otherwise cause every json_schema_validate call to fail at runtime.

A reasonable habit: gate schema deployments through this function in CI, the same way you'd run a YAML or SQL linter.

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 json_schema_validate_schema(definition);
Related functions