Database

Managing JSON and unstructured data

Using the JSON data type in Postgres.

Postgres supports storing and querying unstructured data.

JSON vs JSONB

Postgres supports two types of JSON columns: json (stored as a string) and jsonb (stored as a binary). The recommended type is jsonb for almost all cases.

  • json stores an exact copy of the input text. Database functions must reparse the content on each execution.
  • jsonb stores database in a decomposed binary format. While this makes it slightly slower to input due to added conversion overhead, it is significantly faster to process, since no reparsing is needed.

When to use JSON/JSONB

Generally you should use a jsonb column when you have data that is unstructured or has a variable schema. For example, if you wanted to store responses for various webhooks, you might not know the format of the response when creating the table. Instead, you could store the payload as a jsonb object in a single column.

Don't go overboard with json/jsonb columns. They are a useful tool, but most of the benefits of a relational database come from the ability to query and join structured data, and the referential integrity that brings.

Create JSONB columns

json/jsonb is just another "data type" for Postgres columns. You can create a jsonb column in the same way you would create a text or int column:

Inserting JSON data

You can insert JSON data in the same way that you insert any other data. The data must be valid JSON.

Query JSON data

Querying JSON data is similar to querying other data, with a few other features to access nested values.

Postgres support a range of JSON functions and operators. For example, the -> operator returns values as jsonb data. If you want the data returned as text, use the ->> operator.

Validating JSON data

Supabase provides the pg_jsonschema extension that adds the ability to validate json and jsonb data types against JSON Schema documents.

Once you have enabled the extension, you can add a "check constraint" to your table to validate the JSON data:


_23
create table customers (
_23
id serial primary key,
_23
metadata json
_23
);
_23
_23
alter table customers
_23
add constraint check_metadata check (
_23
json_matches_schema(
_23
'{
_23
"type": "object",
_23
"properties": {
_23
"tags": {
_23
"type": "array",
_23
"items": {
_23
"type": "string",
_23
"maxLength": 16
_23
}
_23
}
_23
}
_23
}',
_23
metadata
_23
)
_23
);

Resources