JSON Schema for DuckDB 2.0: validate a column without losing the bad rows

Finding the first bad document is useful when a query should stop. It is less useful when the job is to tell someone which thousand documents need fixing.

The JSON Schema extension already had json_schema_validate, which raises an error when a document fails validation. That behavior is still there. The DuckDB 2.0 update adds two functions for the other job: keeping validation failures in the result so you can count them, route them, or inspect them alongside the original record.

We also replaced the draft-07 validator with jsoncons. That changes which schema keywords are enforced, so this is an update to check against existing schemas before putting it into an ingestion job.

Keep the invalid rows

json_schema_is_valid(schema, document) answers the question its name asks:

WITH incoming(id, document) AS (
    VALUES (1, '{"quantity": 3}'::JSON),
           (2, '{"quantity": -1}'::JSON),
           (3, '{"quantity": "three"}'::JSON)
)
SELECT id, json_schema_is_valid(
    '{"type": "object", "required": ["quantity"],
      "properties": {"quantity": {"type": "integer", "minimum": 0}}}',
    document
) AS valid
FROM incoming
ORDER BY id;

You can use that boolean in a WHERE clause or an aggregate’s FILTER. A document that does not meet the schema returns false. A malformed schema is still an error: it would be misleading to classify every document as invalid because the rule itself cannot be read. Malformed JSON text can also fail DuckDB’s JSON cast before validation starts. SQL NULL inputs produce SQL NULL, rather than false.

Ask what failed

json_schema_errors returns a list of structs with path, keyword, and message fields. An empty list means the document passed. Unnest the list to get one row per problem:

SELECT e.path, e.keyword, e.message
FROM (
    SELECT unnest(json_schema_errors(
        '{"type": "array", "items": {"type": "integer"}}',
        '[1, "two", 3, true]'
    )) AS e
)
ORDER BY e.path;

These paths are JSON Pointers, so the array positions are zero-based. They identify the failing values without copying the whole document into an error message.

The throwing json_schema_validate now includes up to five validation failures in its message and points to json_schema_errors for the full list. Use the throwing form when failure should stop the statement; use the list when failure is part of the data you want to query.

The schema version now matters

The old validator only supported draft 7. A newer keyword could appear in a schema without doing what its author expected. The new implementation selects the draft from $schema and supports drafts 4, 6, 7, 2019-09, and 2020-12.

For example, 2020-12’s prefixItems checks tuple positions:

SELECT json_schema_is_valid(
    '{"$schema": "https://json-schema.org/draft/2020-12/schema",
      "prefixItems": [{"type": "string"}, {"type": "integer"}]}',
    '[1, "a"]'
) AS valid;

unevaluatedProperties, dependentRequired, and local $defs references are supported too. An unknown schema version raises an error rather than silently falling back.

When $schema is absent, the default is 2020-12. If an existing schema relies on draft-07 tuple rules, declare draft 7 explicitly. Its array-form items and additionalItems have different meanings from the newer tuple vocabulary.

Formats and patterns have explicit rules

The extension now validates format, including email addresses, date-times, and IPv4 addresses:

SELECT json_schema_is_valid('{"format": "email"}', '"not an email"') AS valid;

Formats apply to strings. Add "type": "string" if a non-string value should fail too.

Patterns run through RE2, which avoids recursive matching and catastrophic backtracking. This comes with a compatibility cost: lookaround and backreferences are not supported. A schema containing those constructs is rejected. pattern remains an unanchored search unless you write anchors.

Validation does not fetch remote schemas or read $ref targets from disk. Put shared definitions inside the schema and use local references. Depth checks and a native stack guard also stop excessively nested inputs before they exhaust the process stack.

Existing functions are still useful

json_schema_patch and json_schema_update remain available for applying defaults. They validate the document; they do not turn an invalid value into a valid one by replacing it with a default. Compiled schemas are now cached per execution thread, so using the same schema over a column does not require compiling it for every document.

The implementation change and validation tests cover the draft selection, error lists, formats, and compatibility boundaries described here. The extension documentation has the installation instructions and full function reference. Before upgrading an existing validation pipeline, make its schema version explicit and check its patterns. Those are the two places where an old schema can mean something different in this build.