Skip to content
DuckDB Extensions All extensions JSON Schema

JSON Schema

JSON Schema validation and default-value enrichment in SQL.

32,894
extension loads · last 90 days
On this page

Technical Overview

JSON contracts, made executable in SQL

JSON Schema is the de facto contract language for JSON payloads — a declarative way to say what a document must look like. This extension makes those contracts executable from inside DuckDB, so a data-quality gate, an ETL contract check, or a default-value fill-in can live in SQL instead of a Pydantic / ajv / jsonschema sidecar. It is built on the pboettch/json-schema-validator C++ library, whose primary target is JSON Schema draft-07.

JSON Schema as an in-SQL contract

The idea is to treat a schema as a contract that a column of JSON must satisfy, and to enforce that contract where the data already lives. Validation answers a single question — does this value conform? — which makes it a BOOLEAN predicate that drops straight into WHERE, CASE, and aggregate FILTER clauses. Beyond pure validation, the schema's declared default values can also be projected onto incomplete documents to bring them into conformance.

  • • Draft-07 is the target: The underlying validator primarily targets draft-07. The $schema keyword in your document is honored where the validator supports it; for newer drafts (2019-09, 2020-12), test the specific keywords you use rather than assuming full coverage.
  • • What the validator covers: Coverage matches what draft-07 specifies: type checks (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 that powers default-value enrichment.
  • • Schema and data are both JSON: Both the schema and the value under test are passed as DuckDB JSON values — a column, a struct literal, or a string cast with ::JSON. There's no preregistration step, no ATTACH, no secret type, and no network: validation runs locally against the in-query schema string.
  • • Default-value enrichment, not just validation: The non-obvious capability: turn the schema's declared default values into actual data. One function computes the JSON Patch (RFC 6902) diff of add operations (useful for an audit log); another applies them inline — taking an incomplete payload to a fully populated record in one expression, with no COALESCE cascade.

What to know before relying on it

The extension is small and focused; it doesn't try to be every JSON-Schema validator out there. Knowing its boundaries keeps it in the role it's good at — a coarse SQL-side gate — rather than where a richer tool belongs.

  • • Boolean result, not an error report: Validation returns TRUE / FALSE, not a structured list of which keywords or paths failed. For per-keyword diagnostics, validate at the application layer with ajv, Pydantic, or jsonschema, and use this extension as the coarse SQL gate at ingest, in CI, or in an audit.
  • • No cross-file $ref resolution: Each call takes a single JSON schema value — there is no filesystem or HTTP $ref resolver. Inline any $ref targets into the same schema document before validating.
  • • Prefer typed columns where you can: If the data has a fixed shape, modeling it as proper DuckDB columns plus CHECK constraints is cheaper, clearer, and gives better error messages. JSON Schema validation is for the genuinely-JSON case where the shape is contract-defined but the storage stays opaque.
  • • Per-row cost scales with schema complexity: Deeply-nested schemas with many keywords run the full validator on every row. For very large tables, validate once on ingest into a staging table rather than re-running the check on every read.

Deep Dive

Technical Details

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     json_schema_validate(:schema, payload)) AS valid,
  COUNT(*) FILTER (WHERE NOT json_schema_validate(:schema, payload)) AS invalid
FROM events;

json_schema_validate returns a plain BOOLEAN, so it composes with WHERE, CASE, and aggregate FILTER clauses the way every other DuckDB predicate does — no UDF setup, no detour through Python, no extra round-trip.

Best fit, scoped honestly

This extension is built on the pboettch/json-schema-validator C++ library. Its primary target is JSON Schema draft-07; newer drafts (2019-09 / 2020-12) work for the keywords the underlying validator supports — test the keywords you actually use.

The validation result is a BOOLEAN. There is no structured per-keyword failure report today. There is also no $ref resolver for external files or HTTP — inline any $ref targets into the schema document you pass in.

For application-grade validation with detailed error reports, keep ajv / Pydantic / jsonschema at the API layer. This extension is the right tool when you want the gate to live in SQL — at ingest, in CI, or as part of an audit.

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:

Group Functions Returns
Validate json_schema_validate, json_schema_validate_schema BOOLEAN
Enrich json_schema_patch, json_schema_update JSON

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.

Install

INSTALL json_schema FROM community;
LOAD json_schema;

Quick Start

Validate a JSON document against a schema

SELECT json_schema_validate(:schema, payload) AS ok
FROM events;

Verify the schema itself is well-formed

SELECT json_schema_validate_schema(:schema) AS schema_ok;

Compute a JSON-Patch that fills in missing defaults

SELECT json_schema_patch(:schema, payload) AS patch FROM events;

Or apply the defaults inline

SELECT json_schema_update(:schema, payload) AS enriched FROM events;

Reference

Extension Contents

Quick reference to all available functions and settings organized by category.

Name Description
Enrich

Default-value handling. json_schema_patch computes the JSON Patch (RFC 6902) needed to fill missing defaults; json_schema_update applies it inline.
json_schema_patch() Compute the JSON Patch (RFC 6902) diff that would bring json_data into conformance with the default values declared by the schema.
json_schema_update() Apply the schema's default values inline.
Validate

Conformance checks. json_schema_validate checks a value against a 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() Validate a JSON value against a JSON Schema.
json_schema_validate_schema() Lint a JSON Schema before applying it.

API Reference

Function Documentation

Practical Examples

Cookbook

Real-world recipes and patterns for common use cases.

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     json_schema_validate(:schema, payload)) AS valid,
  COUNT(*) FILTER (WHERE NOT json_schema_validate(:schema, payload)) AS invalid
FROM events;

json_schema_validate returns BOOLEAN, so it slots into FILTER aggregates and WHERE clauses without a wrapper.

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 catches typos in the schema itself — useful as a CI step before a contract update lands. Run it across a registry table to find any bad entries:

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

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     json_schema_validate(:event_schema, payload);
INSERT INTO events_quarantine
SELECT * FROM events_raw
WHERE NOT json_schema_validate(:event_schema, payload);

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 json_schema_validate(:schema, payload);

Validate JSON files on ingest

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

SELECT *
FROM read_json_auto('events/*.json')
WHERE json_schema_validate(:schema, to_json(*));

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 COUNT(*) AS valid
FROM events
WHERE json_schema_validate(:event_schema, payload);

Platform Support

Compatibility

Extension availability may vary by platform and DuckDB version. Check below to ensure this extension supports your environment before installation.

Quick Facts

Release status Stable
Software License MIT
Pricing Free
Written In C++
Source Available Yes
View on GitHub
Usage
32,894
loads · last 90 days

Platforms

  • Linux x86_64 aarch64
  • Linux (musl) Not available
  • macOS Intel Apple Silicon
  • Windows x86_64
  • WASM eh mvp threads
Compiled binary sizes
Platform Architecture Size
Linux x86_64 3.42 MB
Linux aarch64 3.04 MB
macOS Intel 1.61 MB
macOS Apple Silicon 1.47 MB
Windows x86_64 7.51 MB
WASM eh 149.8 KB
WASM mvp 162.6 KB
WASM threads 149.9 KB

Compressed download size from the Haybarn extension repository.

DuckDB & Haybarn

Release calendar