Working with JSON in PostgreSQL

PostgreSQL has first-class support for JSON data. You can store flexible, schema-less documents alongside structured relational data, query deep into nested structures, and index specific paths — all within the same database. This hybrid approach lets you avoid a separate document store for product attributes, event payloads, or user preferences while keeping the relational guarantees for the rest of your data.

JSON vs JSONB

The choice between JSON and JSONB matters for performance and capability. JSON stores the document as-is: original whitespace, duplicate keys, and key order are preserved, but every query re-parses the raw text. JSONB decomposes the document into a binary format on write, removes duplicate keys, and discards key order — but queries are faster and, critically, the column can be indexed.

CREATE TABLE events (
  id      BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL,   -- use JSONB, not JSON
  logged_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO events (payload) VALUES
  ('{"type": "click", "user_id": 42, "page": "/home"}'),
  ('{"type": "purchase", "user_id": 7, "amount": 99.99, "items": [1, 2, 3]}');

Use JSONB by default. The only reason to reach for JSON is when you specifically need to preserve exact input formatting.

Extracting Values: -> and ->>

The -> and ->> operators are the primary tools for reading values out of a JSONB document. Understanding when each returns its result is important — -> keeps the value as JSONB (so you can chain further operators), while ->> converts it to plain text (needed for comparisons and display).

-- -> returns JSON (the value is still JSONB — useful for chaining)
SELECT payload -> 'user_id' FROM events;
-- Result: 42  (type: jsonb)

-- ->> returns TEXT (needed for string comparisons and display)
SELECT payload ->> 'type' FROM events;
-- Result: click  (type: text)

-- Chain -> to navigate nested objects, then ->> at the end to get text
SELECT payload -> 'address' ->> 'city' FROM users_json;

Because ->> returns text, you need to cast when comparing to numbers or dates:

-- Cast the extracted text to NUMERIC for a numeric comparison
SELECT * FROM events
WHERE (payload ->> 'amount')::NUMERIC > 50;

Nested Paths with #> and #>>

For deeply nested structures, #> and #>> take an array of keys instead of requiring you to chain multiple -> operators. They’re cleaner to read when accessing values more than two levels deep.

INSERT INTO events (payload) VALUES
  ('{"user": {"profile": {"city": "Berlin", "age": 29}}}');

-- These two are equivalent — #>> is more readable for deep paths
SELECT payload -> 'user' -> 'profile' ->> 'city' FROM events;
SELECT payload #>> '{user,profile,city}'            FROM events;

-- Array index access (zero-based)
SELECT payload #>> '{items,0}' FROM events;
-- Returns the first element of the "items" array

Building JSON Values

Sometimes you need to construct JSON in a query rather than storing it — for example, to shape API responses or aggregate row data into a document. jsonb_build_object and jsonb_build_array let you do this cleanly from SQL expressions.

-- jsonb_build_object takes alternating key/value pairs
SELECT jsonb_build_object(
  'name',  u.name,
  'email', u.email,
  'since', u.created_at
)
FROM users u WHERE u.id = 1;

-- jsonb_build_array builds a JSON array from a list of values
SELECT jsonb_build_array(1, 'hello', TRUE, NULL);
-- [1, "hello", true, null]

Aggregating Rows into JSON

jsonb_agg collects rows into a JSON array, and jsonb_object_agg builds a JSON object from key-value pairs. These are useful for building nested API responses entirely in SQL, which avoids extra round-trips and application-layer assembly code.

-- Aggregate order items into a JSON array per order
SELECT
  o.id,
  jsonb_agg(
    jsonb_build_object('product', p.name, 'qty', oi.qty)
    ORDER BY p.name
  ) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p    ON p.id = oi.product_id
GROUP BY o.id;

-- Build a settings object from a key-value table
SELECT jsonb_object_agg(key, value)
FROM user_settings
WHERE user_id = 5;
-- {"theme": "dark", "locale": "en-US", "notifications": "true"}

Updating JSONB Values

JSONB columns are immutable at the value level — you can’t update a single key in place. Instead, jsonb_set returns a new JSONB value with one path updated, which you assign back to the column. This is a full column replacement under the hood, but the syntax makes it look like a targeted update.

-- jsonb_set(target, path, new_value, create_if_missing)
UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"Hamburg"', true)
WHERE id = 1;

-- Remove a top-level key with the - operator
UPDATE users
SET profile = profile - 'temporary_token'
WHERE id = 1;

-- Remove a nested key with the #- operator
UPDATE users
SET profile = profile #- '{address,old_zip}'
WHERE id = 1;

jsonb_strip_nulls removes all keys with null values recursively — useful for cleaning up sparse documents before storage:

SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": {"d": null, "e": 2}}');
-- {"a": 1, "c": {"e": 2}}

Containment and Key Existence

The @> operator checks whether the left JSONB value contains the right one — meaning all the keys and values in the right operand are present in the left. This is the most common operator for filtering JSONB and the one that benefits most from a GIN index.

-- Find all click events from user 42
SELECT * FROM events
WHERE payload @> '{"type": "click", "user_id": 42}';

-- Check if a specific key exists at the top level
SELECT * FROM events WHERE payload ? 'amount';

-- Check if any of several keys exist (?|) or all of them (?&)
SELECT * FROM events WHERE payload ?| ARRAY['amount', 'total'];
SELECT * FROM events WHERE payload ?& ARRAY['type', 'user_id'];

Expanding JSONB into Rows

jsonb_each expands a JSONB object into a set of key-value rows. jsonb_array_elements expands a JSON array into individual element rows. These are useful when you need to process the contents of a document relationally — joining against them, counting them, or filtering them with WHERE.

-- Expand top-level keys of a document into rows
SELECT key, value
FROM events, jsonb_each(payload)
WHERE events.id = 1;

-- Expand an array into individual rows for per-element processing
SELECT elem
FROM events, jsonb_array_elements(payload -> 'items') AS elem
WHERE events.id = 2;
-- Returns each item as a separate row: 1, 2, 3

Indexing JSONB

Without an index, every JSONB containment or key-existence query requires a sequential scan. A GIN index on a JSONB column supports @>, ?, ?|, and ?& operators across the entire document and is the default choice for general JSONB querying.

-- GIN index covers all containment and key-existence queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

For queries that always filter on the same specific path, an expression index is smaller and faster than a full GIN index:

-- Expression index on a specific path — smaller, faster for equality lookups
CREATE INDEX idx_events_user_id ON events ((payload ->> 'user_id'));

SELECT * FROM events WHERE payload ->> 'user_id' = '42';

Use the GIN index when your queries search across many different keys. Use expression indexes when you always query the same specific path.

Semi-Structured Data Patterns

The most practical approach is a hybrid schema: structured columns for data you always query and filter on, and a JSONB column for variable attributes that differ by record type. This gives you the indexing and constraint benefits of relational columns for the core data, and the flexibility of a document store for the rest.

CREATE TABLE products (
  id          BIGSERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  price       NUMERIC NOT NULL,
  category    TEXT NOT NULL,
  attributes  JSONB DEFAULT '{}'   -- variable per category: size, color, material, etc.
);

INSERT INTO products (name, price, category, attributes) VALUES
  ('Wool Sweater', 89.99, 'clothing',
   '{"color": "navy", "sizes": ["S","M","L"], "material": "wool"}'),
  ('Running Shoes', 129.99, 'footwear',
   '{"color": "white", "sizes": ["40","41","42"], "waterproof": false}');

-- Filter by relational column (category) and JSONB containment (size "M" in the sizes array)
SELECT name, price
FROM products
WHERE category = 'clothing'
  AND attributes -> 'sizes' @> '"M"';

This keeps common filter columns indexed as regular columns while leaving room for product-specific fields that vary by category.