JSON and JSONB columns

Postgres has two JSON types. jsonb stores a parsed, binary form that you can index and query efficiently. json keeps the exact text you sent, including whitespace and key order. Use jsonb unless you have a specific reason to keep the original text.

JSON columns are useful for data whose shape varies: settings, metadata from a third-party API, form answers, or attributes that differ per product. For data you filter, join or validate all the time, regular columns are faster and safer.

Creating a JSONB column

A team chat app might keep per-message extras in a jsonb column:

create table public.messages (
  id bigint generated always as identity primary key,
  channel_id bigint not null,
  author_id uuid not null default auth.uid() references auth.users (id),
  body text not null,
  meta jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

alter table public.messages enable row level security;

insert into public.messages (channel_id, author_id, body, meta) values
  (1, '6f1c0f7e-2b1d-4c55-9a59-0d7f4c3b8e21', 'Deploy is done',
   '{"client": "desktop", "mentions": ["sam", "lee"], "link": {"url": "https://example.com/build/88", "title": "Build 88"}}');

Reading JSON in SQL

select id, body, meta ->> 'client' as client, meta #>> '{link,url}' as link_url
from public.messages
where meta -> 'mentions' ? 'sam';

Cast text results when you need a number or a date: (meta ->> 'attempts')::int.

Querying JSON with supabase-js

The Data API understands the same arrows in select and in filters.

// Pick fields out of the JSON column
const { data } = await supabase
  .from('messages')
  .select('id, body, client:meta->>client, link_title:meta->link->>title')
  .eq('channel_id', 1)

// Filter on a text value inside the JSON
const { data: desktop } = await supabase
  .from('messages')
  .select('id, body')
  .eq('meta->>client', 'desktop')

// Containment: rows whose meta includes this fragment
const { data: mentionsSam } = await supabase
  .from('messages')
  .select('id, body')
  .contains('meta', { mentions: ['sam'] })

Use ->> when comparing to a string. With -> the value stays JSON, so you'd be comparing against a JSON literal.

Updating part of a document

Replacing the whole value is simple from the client: read it, change it, write it back. To change one key in SQL without touching the rest, use jsonb_set or the || merge operator:

-- set one nested key
update public.messages
set meta = jsonb_set(meta, '{link,title}', '"Build 88 (green)"')
where id = 1;

-- merge in new keys
update public.messages
set meta = meta || '{"edited": true}'::jsonb
where id = 1;

-- remove a key
update public.messages
set meta = meta - 'client'
where id = 1;

If clients need to patch JSON atomically (two people editing different keys at once), wrap the update in a database function and call it with RPC, so the merge happens inside Postgres.

Indexing JSON

Without an index, every JSON filter reads every row. Pick the index for the queries you run:

-- Speeds up @>, ? and similar operators on the whole column
create index messages_meta_gin on public.messages using gin (meta jsonb_path_ops);

-- Speeds up equality on one extracted field, e.g. .eq('meta->>client', 'desktop')
create index messages_meta_client on public.messages ((meta ->> 'client'));

jsonb_path_ops makes a smaller, faster index but only supports containment (@>) and JSON path matching. Leave it off if you also need the key-exists operators (?, ?|, ?&). Check that your query uses the index with explain; see Query performance and EXPLAIN and Indexes.

Validating the shape

Postgres accepts any valid JSON in a jsonb column. Add checks for the parts you rely on:

alter table public.messages
  add constraint meta_is_object check (jsonb_typeof(meta) = 'object'),
  add constraint mentions_is_array check (
    not (meta ? 'mentions') or jsonb_typeof(meta -> 'mentions') = 'array'
  );

For full JSON Schema validation, look for the pg_jsonschema extension in your project's list of available extensions; see Postgres extensions.

When to use a real column instead

Move a value out of JSON into its own column when:

• you filter or sort on it in most queries,
• it's a foreign key to another table,
• an RLS policy depends on it (policies on JSON fields are harder to index and to read),
• you want not null, unique or type checks enforced by the database.

A generated column gives you both: the JSON stays the source, and the column is indexable and typed.

alter table public.messages
  add column client text generated always as (meta ->> 'client') stored;

Related

• Tables, columns and data
• Querying and filtering data
• Full-text search