SQL JSON Operations in PostgreSQL: A Practical Guide

JSON vs JSONB

PostgreSQL offers two JSON column types:

• JSON: Stores the text as-is. Preserves whitespace, key order, and duplicate keys. Every query re-parses the text.
• JSONB: Stores a decomposed binary format. Does not preserve whitespace or key order. Supports indexing and is faster for queries.

Use JSONB in almost all cases. Use JSON only when you must preserve the exact original formatting.

CREATE TABLE events (
  id      SERIAL PRIMARY KEY,
  name    TEXT NOT NULL,
  payload JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO events (name, payload) VALUES
  ('signup', '{"user_id": 1, "plan": "pro", "source": "google"}'),
  ('purchase', '{"user_id": 1, "item": "widget", "price": 29.99, "tags": ["sale", "new"]}'),
  ('signup', '{"user_id": 2, "plan": "free", "source": "organic", "referrer": "blog"}');

Extracting Values

Arrow Operators

-- -> returns JSONB (preserves type)
SELECT payload -> 'plan' FROM events;
-- Returns: "pro" (with quotes, it's a JSONB string)

-- ->> returns TEXT
SELECT payload ->> 'plan' FROM events;
-- Returns: pro (no quotes, plain text)

-- Nested access
SELECT payload -> 'address' -> 'city' FROM events;
SELECT payload -> 'address' ->> 'city' FROM events;

Path Extraction

For deeply nested documents, use #> and #>> with a path array:

-- Example payload: {"user": {"address": {"city": "Denver"}}}
SELECT payload #>> '{user,address,city}' AS city FROM events;

Array Access

JSON arrays use zero-based indexing:

-- Get the first tag
SELECT payload -> 'tags' -> 0 FROM events;
-- Returns: "sale"

SELECT payload -> 'tags' ->> 0 FROM events;
-- Returns: sale

Filtering with JSON

Containment (@>)

The @> operator checks if the left JSONB contains the right JSONB:

-- Find all pro signups
SELECT * FROM events
WHERE payload @> '{"plan": "pro"}';

-- Find purchases with a specific tag
SELECT * FROM events
WHERE payload -> 'tags' @> '["sale"]';

Existence Operators

-- ? checks if a top-level key exists
SELECT * FROM events WHERE payload ? 'referrer';

-- ?| checks if ANY of the keys exist
SELECT * FROM events WHERE payload ?| array['referrer', 'source'];

-- ?& checks if ALL keys exist
SELECT * FROM events WHERE payload ?& array['plan', 'source'];

Comparison on Extracted Values

-- Cast extracted values for comparison
SELECT * FROM events
WHERE (payload ->> 'price')::numeric > 20;

-- Text comparison
SELECT * FROM events
WHERE payload ->> 'source' = 'google';

Building JSON

Constructing JSON Objects

-- From key-value pairs
SELECT jsonb_build_object(
  'name', 'Alice',
  'age', 30,
  'active', true
);
-- Returns: {"age": 30, "name": "Alice", "active": true}

-- From a row
SELECT to_jsonb(e.*) FROM events e WHERE e.id = 1;

Aggregating into JSON

-- Collect rows into a JSON array
SELECT jsonb_agg(
  jsonb_build_object('name', name, 'plan', payload ->> 'plan')
)
FROM events
WHERE name = 'signup';

-- Collect into a keyed object
SELECT jsonb_object_agg(
  payload ->> 'user_id',
  payload ->> 'plan'
)
FROM events
WHERE name = 'signup';

Modifying JSONB

Setting Values

-- Set or add a key
UPDATE events
SET payload = jsonb_set(payload, '{plan}', '"enterprise"')
WHERE id = 1;

-- Set a nested path (creates intermediate keys)
UPDATE events
SET payload = jsonb_set(payload, '{metadata,updated_at}', '"2026-07-01"', true)
WHERE id = 1;

Removing Keys

-- Remove a top-level key
UPDATE events
SET payload = payload - 'referrer'
WHERE id = 3;

-- Remove a nested key
UPDATE events
SET payload = payload #- '{metadata,updated_at}'
WHERE id = 1;

-- Remove an array element by index
UPDATE events
SET payload = payload #- '{tags,0}'
WHERE id = 2;

Merging Objects

The || operator merges two JSONB objects. Right-hand keys win on conflict:

UPDATE events
SET payload = payload || '{"priority": "high", "plan": "enterprise"}'
WHERE id = 1;

Expanding JSON

jsonb_each and jsonb_each_text

-- Expand a JSONB object into key-value rows
SELECT e.id, kv.key, kv.value
FROM events e,
     jsonb_each(e.payload) AS kv;

-- Text version (values are text, not JSONB)
SELECT e.id, kv.key, kv.value
FROM events e,
     jsonb_each_text(e.payload) AS kv;

jsonb_array_elements

-- Expand a JSON array into rows
SELECT e.id, tag
FROM events e,
     jsonb_array_elements_text(e.payload -> 'tags') AS tag
WHERE e.payload ? 'tags';

Indexing JSONB

GIN Index

A GIN index on an entire JSONB column supports containment (@>), existence (?, ?|, ?&), and key-path queries:

CREATE INDEX idx_events_payload ON events USING gin (payload);

-- These queries use the GIN index
SELECT * FROM events WHERE payload @> '{"plan": "pro"}';
SELECT * FROM events WHERE payload ? 'referrer';

GIN with jsonb_path_ops

For containment queries only, jsonb_path_ops creates a smaller, faster index:

CREATE INDEX idx_events_payload_path ON events USING gin (payload jsonb_path_ops);

-- Uses the index
SELECT * FROM events WHERE payload @> '{"source": "google"}';

-- Does NOT use jsonb_path_ops (existence operator)
SELECT * FROM events WHERE payload ? 'source';

B-Tree on Extracted Values

For equality or range queries on a specific key, an expression index is more efficient:

CREATE INDEX idx_events_plan ON events ((payload ->> 'plan'));

-- Uses the B-tree index
SELECT * FROM events WHERE payload ->> 'plan' = 'pro';

JSON Path Queries (PostgreSQL 12+)

PostgreSQL 12 introduced SQL/JSON path queries using jsonb_path_query:

-- Find items where price > 20
SELECT * FROM events
WHERE jsonb_path_exists(payload, '$.price ? (@ > 20)');

-- Extract matching values
SELECT jsonb_path_query(payload, '$.tags[*] ? (@ == "sale")')
FROM events
WHERE name = 'purchase';

When to Use JSONB vs Relational Columns

Use JSONB when:

• The schema varies across rows (event payloads, feature flags).
• You need to store semi-structured data from external APIs.
• The JSON fields are rarely used in JOINs or complex aggregations.

Use relational columns when:

• The field is queried frequently in WHERE clauses.
• The field participates in JOINs or foreign keys.
• You need strict type enforcement and NOT NULL constraints.

A common pattern is to use relational columns for core fields and a JSONB column for extensible metadata:

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  price       NUMERIC(10,2) NOT NULL,
  category_id INT REFERENCES categories(id),
  metadata    JSONB DEFAULT '{}'
);

Summary

PostgreSQL’s JSONB type gives you document-database flexibility within a relational system. Use -> and ->> for extraction, @> for containment checks, jsonb_set and || for mutations, and GIN indexes for fast lookups. Keep frequently queried fields as relational columns and use JSONB for the variable, semi-structured remainder.