# JSON and JSONB columns

> Store, query, filter, update and index JSON data in a SupaHost project, from SQL and through the Data API with supabase-js, and when a regular column is the better choice.

Source: https://supahost.app/docs/database/json · Updated 2026-11-11

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:

```sql
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

| Operator | Returns | Example | Result |
|---|---|---|---|
| `->` | `jsonb` | `meta -> 'link'` | `{"url": "...", "title": "Build 88"}` |
| `->>` | `text` | `meta ->> 'client'` | `desktop` |
| `#>` / `#>>` | path, as `jsonb` / `text` | `meta #>> '{link,title}'` | `Build 88` |
| `@>` | `boolean` (contains) | `meta @> '{"client": "desktop"}'` | `true` |
| `?` | `boolean` (key exists) | `meta ? 'link'` | `true` |

```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.

```ts
// 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:

```sql
-- 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](/docs/database/functions) and call it with [RPC](/docs/api/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:

```sql
-- 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](/docs/database/query-performance) and [Indexes](/docs/database/indexes).

## Validating the shape

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

```sql
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](/docs/database/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.

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

## Related

- [Tables, columns and data](/docs/database/tables)
- [Querying and filtering data](/docs/api/querying)
- [Full-text search](/docs/database/full-text-search)