Skip to content
DuckDB Extensions All extensions JSON Schema

Enrich functions in the JSON Schema DuckDB extension

Function category

Enrich

2 functions

Default-value handling. [`json_schema_patch`](#json_schema_patch) computes the [JSON Patch (RFC 6902)](https://datatracker.ietf.org/doc/html/rfc6902) needed to fill missing `default`s; [`json_schema_update`](#json_schema_update) applies it inline.

json_schema_patch

Scalar function Enrich
Signature
json_schema_patch(schema: JSON, json_data: JSON) → JSON
Arguments (Positional)
Argument schema Type JSON Mode Positional Description JSON Schema document. Properties whose default keyword is set are the ones that contribute to the patch.
Argument json_data Type JSON Mode Positional Description The (possibly incomplete) JSON value. Missing properties for which the schema declares a default will appear in the resulting patch.
Returns

JSON — a JSON Patch (RFC 6902) array of add operations that, when applied, fill in the missing defaults.

Description

Compute the JSON Patch (RFC 6902) diff that would bring json_data into conformance with the default values declared by the schema. Returns the patch — does not apply it (use json_schema_update for the in-place variant).

Use the patch when you want to log or audit which fields were synthesized, or when you need to apply the same diff to multiple downstream representations of the document.

1 Generate a patch for a partial document 
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}]
2 Materialize patches alongside the original payload for auditing 
SELECT
  payload,
  json_schema_patch(:schema, payload) AS defaults_patch
FROM events_raw;
Related functions

json_schema_update

Scalar function Enrich
Signature
json_schema_update(schema: JSON, json_data: JSON) → JSON
Arguments (Positional)
Argument schema Type JSON Mode Positional Description JSON Schema document. Properties with default declared are filled in where the input is missing them.
Argument json_data Type JSON Mode Positional Description The JSON value to enrich. Existing fields are preserved; only missing properties with declared default values are added.
Returns

JSON — the input value with missing defaults filled in.

Description

Apply the schema's default values inline. Equivalent to running json_schema_patch and applying the resulting patch — but in a single function call you can use directly in a SELECT projection.

This is the function to reach for when you want enriched JSON, not a diff.

1 Fill in a missing default 
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"}
2 Build a clean table with defaults applied 
CREATE TABLE events_enriched AS
SELECT json_schema_update(:schema, payload) AS payload
FROM events_raw
WHERE json_schema_validate(:schema, payload);
Related functions