JSONB and arrays

When a document column beats another table, how the JSON operators differ, and how to index both JSONB and arrays properly.

jsonb versus json

SELECT payload -> 'sku'          AS sku_json,     -- jsonb
       payload ->> 'sku'         AS sku_text,     -- text
       payload #> '{ship,zip}'   AS zip_json,     -- path, jsonb
       payload #>> '{ship,zip}'  AS zip_text      -- path, text
FROM   events;

-- containment, key existence, and merge
SELECT id FROM events WHERE payload @> '{"status":"failed"}';
SELECT id FROM events WHERE payload ? 'retry_count';
UPDATE events SET payload = payload || '{"reviewed":true}'::jsonb WHERE id = 7;
UPDATE events SET payload = payload - 'debug_trace' WHERE id = 7;

The single-arrow operators return jsonb and stay in JSON land; the double-arrow operators return text so you can compare against a string or cast it. Forgetting the extra arrow is the most common source of operator does not exist errors.

Indexing JSONB

-- general purpose: supports @>, ?, ?|, ?&
CREATE INDEX idx_events_payload ON events USING gin (payload);

-- containment only: smaller and faster
CREATE INDEX idx_events_payload_path ON events USING gin (payload jsonb_path_ops);

-- frequent equality or sorting on one extracted field
CREATE INDEX idx_events_sku ON events ((payload ->> 'sku'));
CREATE INDEX idx_events_created ON events (((payload ->> 'created_at')::timestamptz));

• A GIN index speeds up containment, not path extraction: payload -> 'sku' = 'X' needs the expression index.
• Expression indexes need the expression written exactly the same way in the query, or the planner will not match it.
• GIN writes are slower than b-tree writes; on write-heavy tables keep the number of GIN indexes small.
• Keep JSONB for genuinely variable attributes. Fields you filter and join on constantly belong in real columns.

Arrays and when to use them

CREATE TABLE articles (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tags text[] NOT NULL DEFAULT '{}'
);

INSERT INTO articles (tags) VALUES (ARRAY['sql', 'postgres', 'indexing']);

SELECT id FROM articles WHERE tags @> ARRAY['postgres'];      -- contains all
SELECT id FROM articles WHERE tags && ARRAY['sql', 'redis'];  -- overlaps
SELECT id, unnest(tags) AS tag FROM articles;                 -- expand to rows
SELECT tag, count(*) FROM articles, unnest(tags) AS tag
GROUP BY tag ORDER BY count(*) DESC LIMIT 10;                 -- tag cloud

CREATE INDEX idx_articles_tags ON articles USING gin (tags);

• Use an array when the values are a small, value-like list you never join to or constrain: tags, flags, a fixed set of channels.
• Use a junction table when the elements are entities with their own attributes you will filter, update or count across.
• Parameter lists map naturally onto arrays: WHERE id = ANY($1::bigint[]) avoids building an IN list of unknown length.
• array_agg collapses rows into an array; combine it with GROUP BY to return one row per parent.

FAQ

Related

psql and the basics of SQL Transactions and MVCC basics

Last refreshed 2026-09-18.