JSONB and arrays

PostgreSQL has two JSON types. json stores the text exactly as given; jsonb parses it into a binary form: keys are deduplicated (the last one wins), key order and whitespace are not kept, and it supports indexing and containment operators. Use jsonb unless you must reproduce the original text byte for byte.

Getting values out

• doc -> 'key' returns jsonb (an object, array, string, number...). On an array, -> 0 is the first element; JSON arrays are 0-based.
• doc ->> 'key' returns text. Use it to compare with a string or to cast: (doc ->> 'qty')::int.
• doc #>> '{user,id}' follows a path and returns text.
• doc @> '{"type": "purchase"}' is containment: does the document include this structure? doc ? 'items' asks whether a top-level key exists.

A missing key is not an error: the operators return NULL, and jsonb_array_length of NULL is NULL, not 0.

1CREATE TABLE events (id int PRIMARY KEY, payload jsonb NOT NULL);2INSERT INTO events VALUES3  (1, '{"type": "purchase", "user": {"id": 7, "country": "GE"}, "items": [{"sku": "tea", "qty": 2}, {"sku": "cup", "qty": 1}]}'),4  (2, '{"type": "refund", "user": {"id": 8}, "items": [{"sku": "tea", "qty": 1}]}'),5  (3, '{"type": "purchase", "user": {"id": 8, "country": "DE"}}');

1SELECT id,2       (payload -> 'user')::text            AS user_jsonb,3       payload -> 'user' ->> 'country'      AS country,4       payload #>> '{user,id}'              AS user_id,5       payload @> '{"type": "purchase"}'    AS is_purchase,6       payload ? 'items'                    AS has_items,7       jsonb_array_length(payload -> 'items') AS item_count8FROM events9ORDER BY id;

Comparisons on ->> are text comparisons: '10' > '9' is false. Cast before comparing numbers.

Arrays inside documents become rows

jsonb_array_elements(doc -> 'items') is a set-returning function: one row per element. Put it in FROM with CROSS JOIN LATERAL (or just a comma) to pair each element with its parent row. A document without the array produces no rows, so a cross join drops it; use LEFT JOIN LATERAL ... ON true if it must stay.

1CREATE FUNCTION attempt(stmt text) RETURNS text LANGUAGE plpgsql AS $$2BEGIN3  EXECUTE stmt;4  RETURN 'ok';5EXCEPTION WHEN OTHERS THEN6  RETURN SQLSTATE || ': ' || SQLERRM;7END $$;8CREATE TABLE events (id int PRIMARY KEY, payload jsonb NOT NULL);9INSERT INTO events VALUES10  (1, '{"type": "purchase", "items": [{"sku": "tea", "qty": 2}, {"sku": "cup", "qty": 1}]}'),11  (2, '{"type": "refund", "items": [{"sku": "tea", "qty": 1}]}'),12  (3, '{"type": "purchase"}');

1SELECT e.id,2       item ->> 'sku'         AS sku,3       (item ->> 'qty')::int  AS qty,4       -- the same constants on every row, to show the traps:5       '{"n": "10"}'::jsonb ->> 'n' > '9'          AS text_compare,6       ('{"n": "10"}'::jsonb ->> 'n')::int > 9     AS int_compare,7       attempt($$SELECT '{"type": "purchase"}'::jsonb -> 'type' = 'purchase'$$) AS arrow_vs_string,8       '{"b": 1, "a": 2, "a": 3}'::jsonb::text     AS jsonb_keeps9FROM events e10CROSS JOIN LATERAL jsonb_array_elements(e.payload -> 'items') AS item11ORDER BY e.id, sku;

PostgreSQL arrays

Native arrays (int[], text[]) are 1-based, and indexing out of range returns NULL rather than raising. x = ANY (arr) tests membership, @> containment, && overlap. unnest(arr) WITH ORDINALITY turns an array into rows with their positions, and array_agg builds one back. An empty array has cardinality 0 but array_length(arr, 1) NULL.

1CREATE TABLE posts (id int PRIMARY KEY, tags text[] NOT NULL);2INSERT INTO posts VALUES (1, '{sql,postgres}'), (2, '{postgres}'), (3, '{}');

1SELECT p.id,2       t.tag,3       t.pos,4       p.tags[1]                   AS first_tag,5       p.tags[0]                   AS zeroth_tag,6       'postgres' = ANY (p.tags)   AS mentions_pg,7       cardinality(p.tags)         AS n_tags8FROM posts p9LEFT JOIN LATERAL unnest(p.tags) WITH ORDINALITY AS t(tag, pos) ON true10ORDER BY p.id, t.pos;

When not to use JSONB

A JSONB column is a good home for data whose shape varies and which you mostly read whole: webhook payloads, settings, audit snapshots. It is a poor home for anything you join on, aggregate across, or must constrain: there are no foreign keys into a document, CHECKs get awkward, and the planner has no per-key statistics for an expression like payload ->> 'type' unless you index that expression or create statistics on it (CREATE STATISTICS accepts expressions since PostgreSQL 14). A GIN index on the column serves @> and ?, which is why containment is the form to prefer for filters; the indexes lesson shows the plan.

Common mistakes

• Comparing doc ->> 'n' > '9' and getting a text comparison.
• Using -> where text was needed: doc -> 'type' = 'purchase' compares jsonb with jsonb, and the right side is not valid JSON, so the query fails to parse it. Use ->>.
• Expanding an array with a cross join and silently losing parent rows that have no array.
• Indexing a PostgreSQL array from 0 and getting NULL.

What interviewers ask

• json versus jsonb? Text kept verbatim versus parsed binary: jsonb drops duplicate keys and order, and supports GIN indexes and @>.
• How do you query rows whose JSON array contains an object with a given field? with a nested pattern (indexable with GIN), or expand with and filter.