JSON Schema

What you can do with one query

Validate every row of a JSON column against a schema in a single SELECT:

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;

json_schema_validate returns TRUE on success and raises an error on failure. The wrapper above converts failures and SQL NULL inputs to FALSE for counting. Validate the schema separately outside TRY first: TRY catches invalid-schema errors as well as nonconforming data.

Architecture

Internally this is a thin DuckDB scalar-function wrapper around two C++ libraries: pboettch/json-schema-validator for the validation engine and nlohmann/json for parsing. Both schema and data are passed as DuckDB JSON values (or struct literals, or strings cast with ::JSON). There is no ATTACH, no secret type, no network call — the validator runs locally against the in-query schema.

Four scalar functions cover the surface:

Schema features in scope match what draft-07 covers: type validation (string, number, integer, boolean, array, object, null), required, properties, numeric minimum / maximum / multipleOf, string minLength / maxLength / pattern / format, array minItems / maxItems / uniqueItems, and the default keyword used by json_schema_patch and json_schema_update.

Pairs with DuckDB’s JSON support

Schema-side validation is one piece; the rest of the JSON pipeline is plain DuckDB. Read JSON files with read_json_auto, pull out fields with json_extract_*, or cast strings with ::JSON — and feed any of those into json_schema_validate. See DuckDB JSON overview for the surrounding toolkit.

Compared to alternatives

• DuckDB CHECK constraints — if your data has a fixed shape, modeling it as proper columns plus CHECK is faster, clearer, and gives you better error messages. Reach for JSON Schema when the payload is genuinely contract-defined JSON and the storage stays opaque.
• Application-layer validators (ajv / Pydantic / jsonschema) — these give detailed per-keyword failure reports and richer draft coverage. Pair them at the API boundary; use this extension as the SQL-side gate inside DuckDB so the database doesn’t depend on a Python service to enforce its contracts.
• Manual SQL predicates — handcrafted WHEREs over json_extract_* get unwieldy fast for non-trivial schemas. Pointing json_schema_validate at a versioned schema file keeps the contract in one place.

Default-value enrichment

The non-obvious capability: turn the schema’s declared default values into actual data. json_schema_patch returns a JSON Patch (RFC 6902) array of add operations; json_schema_update applies them inline. Together they let you go from “incoming partial payload” to “fully populated record” in one SQL expression — no per-field COALESCE cascade, no application-layer enrichment code.

Validate one 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 valid;
-- TRUE

The schema document follows the JSON Schema specification. See json_schema_validate.

Validate a column

Most real workloads point validation at a JSON column instead of a literal:

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;

json_schema_validate returns TRUE for valid data and raises an error for invalid data. TRY turns errors into NULL; COALESCE(..., false) counts errors and SQL NULL inputs as invalid. Validate the schema once outside TRY before processing rows, because TRY also catches invalid-schema errors. Here :schema and :event_schema stand for schema values supplied by your application.

Lint the schema before deploying

Before pushing a schema to production, verify it’s well-formed:

SELECT json_schema_validate_schema('{
  "$schema": "https://json-schema.org/draft-07/schema",
  "type": "object",
  "properties": { "id": { "type": "integer" } }
}') AS schema_ok;
-- TRUE

json_schema_validate_schema checks whether the validator can load a schema, raising on errors. It is not exhaustive meta-schema validation and may accept unsupported keyword values. To find entries that fail to load without aborting the registry scan:

SELECT name, version
FROM schema_registry
WHERE NOT COALESCE(TRY(json_schema_validate_schema(definition)), false);

Quality-gate filter at ingest

Reject non-conforming payloads at the boundary; tee the rest into a quarantine table for review:

INSERT INTO events_clean
SELECT * FROM events_raw
WHERE     COALESCE(TRY(json_schema_validate(:event_schema, payload)), false);

INSERT INTO events_quarantine
SELECT * FROM events_raw
WHERE NOT COALESCE(TRY(json_schema_validate(:event_schema, payload)), false);

Compute a defaults patch

Given a schema with default values declared, json_schema_patch returns the JSON Patch (RFC 6902) add operations needed to fill in the missing fields:

SELECT json_schema_patch('{
  "$schema": "https://json-schema.org/draft-07/schema",
  "type": "object",
  "properties": {
    "id":   {"type": "integer", "default": 5},
    "name": {"type": "string"}
  }
}', {'name': 'George'}) AS patch;
-- [{"op":"add","path":"/id","value":5}]

Useful when you want to log or audit which fields were synthesized, or when the same diff has to apply to multiple downstream representations.

Apply defaults inline

When you want enriched JSON rather than the diff, reach for json_schema_update instead:

SELECT json_schema_update('{
  "$schema": "https://json-schema.org/draft-07/schema",
  "type": "object",
  "properties": {
    "id":   {"type": "integer", "default": 5},
    "name": {"type": "string"}
  }
}', {'name': 'George'}) AS updated;
-- {"id":5,"name":"George"}

Build a clean enriched table in one step — validate, then enrich:

CREATE TABLE events_enriched AS
SELECT json_schema_update(:schema, payload) AS payload
FROM events_raw
WHERE COALESCE(TRY(json_schema_validate(:schema, payload)), false);

Validate JSON files on ingest

Pair with DuckDB’s read_json_auto for file-based inputs:

SELECT *
FROM read_json_auto('events/*.json') AS event
WHERE COALESCE(TRY(json_schema_validate(:schema, to_json(event))), false);

For records arriving as already-typed JSON columns, pass the column directly without the to_json wrap.

Use a schema parameter

Keep the schema out of query text by binding it as a SQL variable — handy when the same query runs against different contracts:

SET VARIABLE event_schema = (SELECT definition FROM schema_registry WHERE name = 'events.v3');
SELECT json_schema_validate_schema(getvariable('event_schema'));

SELECT COUNT(*) AS valid
FROM events
WHERE COALESCE(TRY(json_schema_validate(getvariable('event_schema'), payload)), false);