JSONB and Arrays

Overview

jsonb: Binary, decomposed JSON — indexable, deduplicated keys, no key order preserved. Prefer it over json (text, re-parsed on every access)
Arrays: Any type can be an array (text[], int[]); 1-based indexing
When to Use: Sparse/variable attributes, external payloads, tags. Not for data you join, constrain with FKs, or update field-by-field at high frequency
Sample Data: products.attributes jsonb and products.tags text[] in E-commerce Practice Queries

JSONB Operators

Access and Extraction

Operator	Returns	Example	Result
→	jsonb	`attributes -> 'specs'`	`{"cpu": "M4"}`
→>	text	`attributes ->> 'brand'`	`Zen`
#>	jsonb at path	`attributes #> '{specs,cpu}'`	`"M4"`
#>>	text at path	`attributes #>> '{specs,cpu}'`	`M4`
[ ] (PG 14+)	jsonb subscript	`attributes['specs']['cpu']`	`"M4"`

SELECT sku,
       attributes -> 'brand'               AS brand_json,     -- "Zen" (jsonb, quoted)
       attributes ->> 'brand'              AS brand_text,     -- Zen  (text)
       (attributes ->> 'ram_gb')::int      AS ram_gb,         -- cast text → int for math
       attributes #>> '{specs,cpu}'        AS cpu,
       attributes['color']                 AS color_subscript
FROM products
WHERE category_id = 3;

→ vs →>: Compare ->> results to text, -> results to jsonb. attributes -> 'brand' = 'Zen' fails (needs '"Zen"'::jsonb)
Missing Key: Returns NULL, no error

Containment and Existence

Operator	Meaning	GIN-Indexable
@>	Left contains right	Yes (both opclasses)
<@	Left contained in right	No (neither opclass)
?	Top-level key/array element exists	Yes (jsonb_ops only)
?\|	Any of these keys exist	Yes (jsonb_ops only)
?&	All of these keys exist	Yes (jsonb_ops only)
@?	JSONPath returns any item	Yes
@@	JSONPath predicate is true	Yes

-- Containment: products whose attributes include brand = Clicky AND wireless = true
SELECT sku, name FROM products
WHERE attributes @> '{"brand": "Clicky", "wireless": true}';
 
-- Nested containment
SELECT sku FROM products WHERE attributes @> '{"specs": {"cpu": "M4"}}';
 
-- Key existence
SELECT sku FROM products WHERE attributes ? 'author';                 -- books
SELECT sku FROM products WHERE attributes ?| ARRAY['author', 'switch'];
SELECT sku FROM products WHERE attributes ?& ARRAY['brand', 'color', 'storage_gb'];

psql / JDBC Gotcha: ? clashes with JDBC placeholders — use jsonb_exists(col, 'key') or ?? escaping in drivers

SQL/JSON Path (PG 12+)

-- Products with more than 16 GB RAM, via a JSONPath predicate (@@) and query (@?)
SELECT sku FROM products WHERE attributes @@ '$.ram_gb > 16';
SELECT sku FROM products WHERE attributes @? '$ ? (@.storage_gb >= 256)';
 
-- Extract with jsonb_path_query / _first
SELECT sku, jsonb_path_query_first(attributes, '$.specs.cpu') AS cpu
FROM products WHERE attributes ? 'specs';
 
-- Variables in paths
SELECT sku FROM products
WHERE jsonb_path_exists(attributes, '$.pages ? (@ > $min)', '{"min": 300}');

PG 17 SQL/JSON Functions

SELECT JSON_VALUE(attributes, '$.brand')                    AS brand,
       JSON_VALUE(attributes, '$.ram_gb' RETURNING int)     AS ram,
       JSON_EXISTS(attributes, '$.specs.cpu')               AS has_cpu,
       JSON_QUERY(attributes, '$.specs')                    AS specs
FROM products
WHERE category_id = 3;
 
-- JSON_TABLE: turn JSON into rows/columns
SELECT jt.*
FROM JSON_TABLE(
    '[{"sku":"A","qty":2},{"sku":"B","qty":5}]'::jsonb, '$[*]'
    COLUMNS (sku text PATH '$.sku', qty int PATH '$.qty')
) AS jt;

Modifying JSONB

Operation	Syntax
Merge / overwrite keys	`col \|\| '{"k": "v"}'` (shallow)
Remove key	`col - 'k'`, `col - ARRAY['a','b']`
Remove nested path	`col #- '{specs,cpu}'`
Set nested value	`jsonb_set(col, '{specs,cpu}', '"M5"', create_missing => true)`
Insert into array	`jsonb_insert(col, '{list,0}', '"x"')`
Subscript assignment (PG 14+)	`UPDATE t SET col['k'] = '"v"'`

BEGIN;
UPDATE products
SET attributes = attributes || '{"warranty_years": 2}'          -- add/overwrite key
WHERE category_id = 3;
 
UPDATE products
SET attributes = jsonb_set(attributes, '{specs,gpu}', '"10-core"', true)
WHERE sku = 'LAP-002';
 
UPDATE products SET attributes['color'] = '"space grey"' WHERE sku = 'LAP-002';
 
UPDATE products SET attributes = attributes - 'warranty_years' WHERE sku = 'LAP-001';
 
SELECT sku, attributes FROM products WHERE category_id = 3 ORDER BY sku;
ROLLBACK;

Whole-Value Rewrite: Any change rewrites the entire jsonb value (and row version) — big documents updated often = bloat and WAL churn

Building and Unpacking JSON

-- Rows → JSON
SELECT jsonb_build_object('id', id, 'name', full_name, 'country', country) AS user_json
FROM users WHERE id <= 2;
 
SELECT jsonb_agg(jsonb_build_object('sku', sku, 'price', price) ORDER BY price DESC) AS catalog
FROM products WHERE category_id = 5;
 
SELECT to_jsonb(u) - 'created_at' AS whole_row FROM users u WHERE id = 1;
 
-- JSON → rows
SELECT p.sku, kv.key, kv.value
FROM products p, jsonb_each(p.attributes) AS kv
WHERE p.sku = 'ACC-002';
 
SELECT * FROM jsonb_to_record('{"sku":"X-1","price":9.5,"stock":3}') AS r(sku text, price numeric, stock int);
 
SELECT jsonb_array_elements_text('["a","b","c"]') AS item;
 
-- Which keys are used across all products, and how often
SELECT key, count(*) FROM products, jsonb_object_keys(attributes) AS key
GROUP BY key ORDER BY count(*) DESC, key;

Indexing JSONB

Options

Index	Serves	Notes
GIN (jsonb_ops)	`@>`, `?`, `?	`,` ?&`,` @?`,` @@`
GIN (jsonb_path_ops)	`@>`, `@?`, `@@`	~2–3× smaller, faster containment; no key-exists
B-tree expression	`=`, range, `ORDER BY` on one extracted field	`((attributes ->> 'brand'))`
Partial	Combine with a predicate	`WHERE attributes ? 'author'`

CREATE INDEX products_attr_gin       ON products USING gin (attributes);                  -- jsonb_ops
CREATE INDEX products_attr_path_gin  ON products USING gin (attributes jsonb_path_ops);
CREATE INDEX products_brand_idx      ON products ((attributes ->> 'brand'));              -- note double parens
CREATE INDEX products_ram_idx        ON products (((attributes ->> 'ram_gb')::int))
    WHERE attributes ? 'ram_gb';
 
-- The query must match the indexed expression exactly
SET enable_seqscan = off;    -- tiny table: force the planner to show index usage
EXPLAIN (COSTS OFF) SELECT sku FROM products WHERE attributes @> '{"brand": "Zen"}';
EXPLAIN (COSTS OFF) SELECT sku FROM products WHERE attributes ->> 'brand' = 'Zen';
EXPLAIN (COSTS OFF) SELECT sku FROM products WHERE attributes ? 'author';
RESET enable_seqscan;

Containment vs Extraction: attributes @> '{"brand":"Zen"}' uses GIN; attributes ->> 'brand' = 'Zen' needs the B-tree expression index — pick one query style and index for it
Statistics: The planner has poor stats inside jsonb; a frequently filtered field may deserve a real column (or a generated column)

-- Promote a hot JSON field to a real, indexable, typed column
ALTER TABLE products
    ADD COLUMN brand text GENERATED ALWAYS AS (attributes ->> 'brand') STORED;
CREATE INDEX products_brand_col_idx ON products (brand);
SELECT brand, count(*) FROM products GROUP BY brand ORDER BY brand;

Arrays

Operators and Functions

Operator / Function	Meaning	Example
arr[1]	Element (1-based)	`tags[1]`
arr[1:2]	Slice	`tags[1:2]`
= ANY(arr)	Element in array	`'sql' = ANY(tags)`
@>	Contains all	`tags @> '{laptop,pro}'`
<@	Is contained by	`tags <@ '{book,sql,systems}'`
&&	Overlaps (any in common)	`tags && '{phone,ios}'`
\|\|	Concatenate / append	`tags \|\| 'sale'`
array_length(arr, 1) / cardinality(arr)	Length
unnest(arr)	Array → rows
array_agg(x)	Rows → array
array_remove / array_append / array_position	Edit / search

SELECT sku, tags, tags[1] AS first_tag, cardinality(tags) AS n_tags
FROM products
WHERE tags && ARRAY['phone', 'book']           -- any overlap
ORDER BY sku;
 
SELECT sku FROM products WHERE tags @> ARRAY['laptop', 'pro'];
SELECT sku FROM products WHERE 'kitchen' = ANY (tags);
 
-- Tag cloud
SELECT tag, count(*) FROM products, unnest(tags) AS tag
GROUP BY tag HAVING count(*) > 1 ORDER BY count(*) DESC, tag;
 
BEGIN;
UPDATE products SET tags = array_append(tags, 'sale') WHERE category_id = 8;
UPDATE products SET tags = array_remove(tags, 'sql')  WHERE sku = 'BOK-001';
SELECT sku, tags FROM products WHERE category_id = 8 ORDER BY sku;
ROLLBACK;
 
-- GIN index for @>, <@, &&
CREATE INDEX products_tags_gin ON products USING gin (tags);

= ANY(tags) and GIN: 'x' = ANY(tags) can’t use a GIN index — write tags @> ARRAY['x'] instead
unnest WITH ORDINALITY: Get the position too — unnest(tags) WITH ORDINALITY AS t(tag, pos)

JSONB vs Columns vs Arrays

Need	Use
Fixed, queried, constrained fields	Real columns
Variable per-row attributes, read mostly	jsonb (+ GIN)
Small list of scalar labels	Array (+ GIN)
Many-to-many with integrity	Join table with FKs
Raw webhook payload for audit	jsonb (maybe no index)

Interview Questions

“json vs jsonb?”: jsonb is binary, indexable, faster to query; json keeps exact text (whitespace, key order, duplicates)
”→ vs →>?”: -> returns jsonb, ->> returns text
“Index a JSONB column?”: GIN for containment/key-exists; expression B-tree for one field equality/range
“When not to use JSONB?”: Fields you join on, need FKs/constraints for, or update constantly
“Find rows whose tags include X?”: tags @> ARRAY['X'] with a GIN index

Summary

Access: -> jsonb, ->> text, #>/#>> paths, subscripts in PG 14+
Query: @> containment, ? key existence, JSONPath @?/@@, SQL/JSON functions in PG 17
Modify: ||, -, #-, jsonb_set, subscript assignment — each rewrites the whole value
Index: GIN jsonb_ops vs jsonb_path_ops; expression or generated-column B-trees for hot fields
Arrays: @>, &&, ANY, unnest, array_agg; GIN for containment/overlap

Kaushik's Notes

Explorer

JSONB and Arrays

Overview

JSONB Operators

Access and Extraction

Containment and Existence

SQL/JSON Path (PG 12+)

PG 17 SQL/JSON Functions

Modifying JSONB

Building and Unpacking JSON

Indexing JSONB

Options

Arrays

Operators and Functions

JSONB vs Columns vs Arrays

Interview Questions

Summary

Graph View

Table of Contents

Backlinks