DuckDB JSON Schema Extension by Query.Farm

The JSON Schema extension, developed by Query.Farm, brings powerful JSON schema validation and data enrichment capabilities directly to your SQL queries in DuckDB. Validate JSON data against schemas, ensure data integrity, and automatically apply default values using the industry-standard JSON Schema specification without leaving your database environment.

Documentation

Full documentation, including installation, usage, the function reference, and cookbook examples, is available at:

https://query.farm/products/extensions/json_schema

Installation

INSTALL json_schema FROM community;
LOAD json_schema;

Validation errors and boolean filters

Both validation functions return TRUE on success and raise an error on failure. They do not return FALSE for a nonconforming document or invalid schema. Validate the schema separately before using TRY to classify rows, because TRY also catches schema errors. This example treats errors and SQL NULL as invalid:

SELECT json_schema_validate_schema('{"type":"integer"}');
SELECT payload,
       COALESCE(TRY(json_schema_validate('{"type":"integer"}', payload)), false) AS valid
FROM (VALUES ('3'::JSON), ('"five"'::JSON), (NULL::JSON)) AS input(payload);