pg_jsonschema

https://github.com/supabase/pg_jsonschema
Admin
pg_jsonschema is a PostgreSQL extension that adds support for JSON schema validation on `json` and...
Tokens:879
Snippets:5
Trust Score:10
License:Apache-2.0
Update:1 year ago
Show doc for...
Tokens:
Raw
### Run pg_jsonschema Installation

Source: https://github.com/supabase/pg_jsonschema/blob/master/README.md

This shell command initiates the installation and setup of the pg_jsonschema extension using Cargo, the Rust package manager. It typically launches a psql prompt for further interaction.

```shell
cargo pgrx run
```

--------------------------------

### Install and Test pg_jsonschema

Source: https://github.com/supabase/pg_jsonschema/blob/master/README.md

This snippet shows the process of installing the `pg_jsonschema` extension in PostgreSQL and then performing a basic validation using the `json_matches_schema` function. It's a quick way to verify the extension is working correctly.

```sql
create extension pg_jsonschema;

select json_matches_schema('{"type": "object"}', '{}');
```

--------------------------------

### Define JSON Schema Validation for JSON Columns

Source: https://github.com/supabase/pg_jsonschema/blob/master/README.md

This SQL snippet demonstrates how to create a table with a JSON column and enforce a JSON schema using the `json_matches_schema` function as a check constraint. It shows an example of a valid and an invalid payload insertion.

```sql
create extension pg_jsonschema;

create table customer(
    id serial primary key,
    metadata json,

    check (
        json_matches_schema(
            '{                 "type": "object",                 "properties": {                     "tags": {                         "type": "array",                         "items": {                             "type": "string",                             "maxLength": 16                         }                     }                 }             }',
            metadata
        )
    )
);

-- Example: Valid Payload
insert into customer(metadata)
values ('{"tags": ["vip", "darkmode-ui"]}');

-- Example: Invalid Payload
insert into customer(metadata)
values ('{"tags": [1, 3]}');
```

--------------------------------

### Benchmark JSONB Schema Validation Performance

Source: https://github.com/supabase/pg_jsonschema/blob/master/README.md

This SQL snippet sets up a benchmark test by creating a table with a JSONB column, defining a check constraint using `jsonb_matches_schema`, and then inserting a large number of records. It's used to measure the performance of the extension against a specific schema.

```sql
create table bench_test_pg_jsonschema(
    meta jsonb,
    check (
        jsonb_matches_schema(
            '{"type": "object", "properties": {"a": {"type": "number"}, "b": {"type": "string"}}}',
            meta
        )
    )
);

insert into bench_test_pg_jsonschema(meta)
select
    json_build_object(
        'a',
        i,
        'b',
        i::text
    )
from
    generate_series(1, 20000) t(i);
```

--------------------------------

### JSON Schema Validation Functions

Source: https://github.com/supabase/pg_jsonschema/blob/master/README.md

This section details the SQL functions provided by the pg_jsonschema extension for validating JSON data against JSON schemas.

```APIDOC
## SQL Functions for JSON Schema Validation

### Description
These functions allow you to validate JSON data against a specified JSON schema within your PostgreSQL database.

### Functions

- **json_matches_schema(schema json, instance json) returns bool**
  - Validates a `json` data type instance against a JSON schema.

- **jsonb_matches_schema(schema json, instance jsonb) returns bool**
  - Validates a `jsonb` data type instance against a JSON schema.

- **jsonschema_is_valid(schema json) returns bool**
  - Validates whether a given JSON object is a valid JSON schema.

### Usage Example

```sql
-- Create a table with a JSON column and a check constraint using json_matches_schema
create table customer(
    id serial primary key,
    metadata json,

    check (
        json_matches_schema(
            '{
                "type": "object",
                "properties": {
                    "tags": {
                        "type": "array",
                        "items": {
                            "type": "string",
                            "maxLength": 16
                        }
                    }
                }
            }',
            metadata
        )
    )
);

-- Example of a valid insert:
insert into customer(metadata)
values ('{"tags": ["vip", "darkmode-ui"]}');

-- Example of an invalid insert (violates schema):
insert into customer(metadata)
values ('{"tags": [1, 3]}');
```

### Error Handling
- Invalid schemas or data types will result in a boolean `false` return or a constraint violation error during insertion/update.
```
Tokens:
Raw