mirror of
https://github.com/supabase/supabase.git
synced 2026-05-11 10:49:48 +08:00
65c6929414
## I have read the [CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md) file. YES ## What kind of change does this PR introduce? Updates verbiage throughout docs to use postgres over postgresql. <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **Documentation** * Updated terminology throughout documentation, guides, and resources for consistent product naming across all user-facing materials, including page titles, descriptions, and reference documentation. <!-- end of auto-generated comment: release notes by coderabbit.ai -->
108 lines
3.2 KiB
Plaintext
108 lines
3.2 KiB
Plaintext
---
|
|
id: 'pg_jsonschema'
|
|
title: 'pg_jsonschema: JSON Schema Validation'
|
|
description: 'Validate json/jsonb with JSON Schema in Postgres.'
|
|
tocVideo: 'amJo48ChLGs'
|
|
---
|
|
|
|
[JSON Schema](https://json-schema.org) is a language for annotating and validating JSON documents. [`pg_jsonschema`](https://github.com/supabase/pg_jsonschema) is a Postgres extension that adds the ability to validate Postgres's built-in `json` and `jsonb` data types against JSON Schema documents.
|
|
|
|
## Enable the extension
|
|
|
|
<Tabs
|
|
scrollable
|
|
size="small"
|
|
type="underlined"
|
|
defaultActiveId="dashboard"
|
|
queryGroup="database-method"
|
|
>
|
|
<TabPanel id="dashboard" label="Dashboard">
|
|
|
|
1. Go to the [Database](/dashboard/project/_/database/tables) page in the Dashboard.
|
|
2. Click on **Extensions** in the sidebar.
|
|
3. Search for `pg_jsonschema` and enable the extension.
|
|
|
|
</TabPanel>
|
|
<TabPanel id="sql" label="SQL">
|
|
|
|
{/* prettier-ignore */}
|
|
```sql
|
|
-- Enable the "pg_jsonschema" extension
|
|
create extension pg_jsonschema with schema extensions;
|
|
|
|
-- Disable the "pg_jsonschema" extension
|
|
drop extension if exists pg_jsonschema;
|
|
```
|
|
|
|
Even though the SQL code is `create extension`, this is the equivalent of enabling the extension.
|
|
To disable an extension you can call `drop extension`.
|
|
|
|
It's good practice to create the extension within a separate schema (like `extensions`) to keep the `public` schema clean.
|
|
|
|
</TabPanel>
|
|
</Tabs>
|
|
|
|
## Functions
|
|
|
|
- [`json_matches_schema(schema json, instance json)`](https://github.com/supabase/pg_jsonschema#api): Checks if a `json` _instance_ conforms to a JSON Schema _schema_.
|
|
- [`jsonb_matches_schema(schema json, instance jsonb)`](https://github.com/supabase/pg_jsonschema#api): Checks if a `jsonb` _instance_ conforms to a JSON Schema _schema_.
|
|
|
|
## Usage
|
|
|
|
Since `pg_jsonschema` exposes its utilities as functions, we can execute them with a select statement:
|
|
|
|
{/* prettier-ignore */}
|
|
```sql
|
|
select
|
|
extensions.json_matches_schema(
|
|
schema := '{"type": "object"}',
|
|
instance := '{}'
|
|
);
|
|
```
|
|
|
|
`pg_jsonschema` is generally used in tandem with a [check constraint](https://www.postgresql.org/docs/current/ddl-constraints.html) as a way to constrain the contents of a json/b column to match a JSON Schema.
|
|
|
|
{/* prettier-ignore */}
|
|
```sql
|
|
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"]}');
|
|
-- Result:
|
|
-- INSERT 0 1
|
|
|
|
-- Example: Invalid Payload
|
|
insert into customer(metadata)
|
|
values ('{"tags": [1, 3]}');
|
|
-- Result:
|
|
-- ERROR: new row for relation "customer" violates check constraint "customer_metadata_check"
|
|
-- DETAIL: Failing row contains (2, {"tags": [1, 3]}).
|
|
```
|
|
|
|
## Resources
|
|
|
|
- Official [`pg_jsonschema` documentation](https://github.com/supabase/pg_jsonschema)
|