JSON & JSONB

PostgreSQL has first-class support for JSON data with the JSON and JSONB types, plus a rich set of operators and functions. JSONB (binary JSON) is almost always what you want — it's parsed, indexed, and enables powerful queries without writing application-side parsing code.

JSON vs JSONB

Always use JSONB unless you specifically need to preserve the exact original JSON text (whitespace, key order, duplicate keys).

CREATE TABLE user_profiles (
    user_id     INTEGER PRIMARY KEY,
    username    TEXT,
    preferences JSONB,  -- use JSONB
    raw_payload JSON    -- only if exact text preservation matters
);

Creating JSONB Data

-- Insert JSON directly
INSERT INTO user_profiles (user_id, username, preferences) VALUES
(1, 'alice', '{"theme": "dark", "notifications": true, "language": "en"}'),
(2, 'bob',   '{"theme": "light", "language": "fr", "tags": ["sql", "postgres"]}'),
(3, 'carol', '{"theme": "dark", "notifications": false, "score": 9.5}');

-- Build JSON using functions
INSERT INTO user_profiles (user_id, username, preferences) VALUES (
    4, 'dave',
    JSONB_BUILD_OBJECT(
        'theme', 'light',
        'language', 'de',
        'active', true
    )
);

Extracting Data — Operators

-- Get a top-level key as JSON
SELECT preferences->'theme' FROM user_profiles WHERE user_id = 1;
-- Result: "dark"   (JSON string with quotes)

-- Get as text (more useful for comparisons)
SELECT preferences->>'theme' FROM user_profiles WHERE user_id = 1;
-- Result: dark   (plain text, no quotes)

-- Navigate nested objects
SELECT data->>'city'
FROM (VALUES ('{"address": {"city": "Paris", "zip": "75001"}}'::jsonb)) AS t(data)
     -- #>> is more convenient for paths:
SELECT data#>>'{address,city}' AS city FROM ... ;
-- Result: Paris

-- Access array elements (0-based)
SELECT preferences->'tags'->0 FROM user_profiles WHERE user_id = 2;
-- Result: "sql"

SELECT preferences->'tags'->>1 FROM user_profiles WHERE user_id = 2;
-- Result: postgres

Filtering with JSONB Operators

-- Find users with dark theme
SELECT username FROM user_profiles
WHERE preferences @> '{"theme": "dark"}';
-- alice, carol

-- Find users with notifications enabled
SELECT username FROM user_profiles
WHERE preferences @> '{"notifications": true}';
-- alice

-- Find users who have a 'language' key
SELECT username FROM user_profiles
WHERE preferences ? 'language';
-- alice, bob, carol, dave

-- Find users who have either 'tags' or 'score'
SELECT username FROM user_profiles
WHERE preferences ?| ARRAY['tags', 'score'];
-- bob (has tags), carol (has score)

-- Find users who have BOTH 'theme' and 'language'
SELECT username FROM user_profiles
WHERE preferences ?& ARRAY['theme', 'language'];
-- alice, bob, carol, dave

Modifying JSONB

jsonb_set — Update a Field

-- Update a specific key (non-destructive — keeps other keys)
UPDATE user_profiles
SET preferences = JSONB_SET(preferences, '{theme}', '"auto"')
WHERE user_id = 1;

-- Add a new nested key
UPDATE user_profiles
SET preferences = JSONB_SET(preferences, '{address,city}', '"London"', true)
-- true = create the path if it doesn't exist
WHERE user_id = 3;

-- Update an array element
UPDATE user_profiles
SET preferences = JSONB_SET(preferences, '{tags,0}', '"database"')
WHERE user_id = 2;

jsonb_insert — Insert Into Array or Object

-- Insert at index 1 of an array (shifts elements right)
UPDATE user_profiles
SET preferences = JSONB_INSERT(preferences, '{tags,1}', '"beginner"')
WHERE user_id = 2;
-- tags was: ["sql", "postgres"]
-- tags now: ["sql", "beginner", "postgres"]

Concatenation with ||

-- Merge/add fields (rightmost wins on key conflict)
UPDATE user_profiles
SET preferences = preferences || '{"notifications": true, "newsletter": false}'
WHERE user_id = 2;

-- Remove a key with - operator
UPDATE user_profiles
SET preferences = preferences - 'newsletter'
WHERE user_id = 2;

-- Remove multiple keys
UPDATE user_profiles
SET preferences = preferences - ARRAY['newsletter', 'raw_data']
WHERE user_id = 3;

-- Remove by path (nested)
UPDATE user_profiles
SET preferences = preferences #- '{address,zip}'
WHERE user_id = 3;

Indexing JSONB

GIN Index for Containment Queries

-- Full GIN index — supports @>, ?, ?|, ?&
CREATE INDEX user_prefs_gin ON user_profiles USING GIN (preferences);

-- Smaller index — only supports @> (faster build, smaller size)
CREATE INDEX user_prefs_gin_path ON user_profiles USING GIN (preferences jsonb_path_ops);

B-tree Index on a JSONB Expression

-- Index on a specific JSON field for equality queries
CREATE INDEX user_prefs_theme ON user_profiles ((preferences->>'theme'));

-- Now this uses the index:
SELECT * FROM user_profiles WHERE preferences->>'theme' = 'dark';

JSON Path Queries

PostgreSQL 12+ introduced jsonb_path_query for XPath-style path expressions:

-- Find items where price > 20 in a JSON array
SELECT jsonb_path_query(
    '[{"name":"Widget","price":15},{"name":"Gadget","price":49.99}]',
    '$[*] ? (@.price > 20)'
);
-- Returns: {"name": "Gadget", "price": 49.99}

-- Use in WHERE
SELECT * FROM products_json
WHERE preferences @@ '$.score > 9'::jsonpath;
-- @@ is the jsonpath match operator

Aggregating to JSON

-- Aggregate rows into a JSON array
SELECT JSON_AGG(
    JSON_BUILD_OBJECT('id', id, 'name', username)
    ORDER BY username
) AS users
FROM user_profiles;
-- [{"id":1,"name":"alice"},{"id":2,"name":"bob"},...]

-- Build a JSON object with key → value from pairs
SELECT JSONB_OBJECT_AGG(username, preferences->>'theme') AS theme_map
FROM user_profiles;
-- {"alice":"dark","bob":"light","carol":"dark","dave":"light"}

Expanding JSON

-- Expand JSONB object into key-value rows
SELECT key, value
FROM user_profiles, JSONB_EACH(preferences)
WHERE user_id = 1;

-- key          | value
-- -------------|------
-- theme        | "dark"
-- notifications| true
-- language     | "en"

-- Text version
SELECT key, value
FROM user_profiles, JSONB_EACH_TEXT(preferences)
WHERE user_id = 1;

-- Expand a JSONB array into rows
SELECT value FROM JSONB_ARRAY_ELEMENTS('[1, 2, 3, 4, 5]'::jsonb);
-- 1, 2, 3, 4, 5 (each as a separate row)

SELECT value->>'name' AS tag
FROM user_profiles, JSONB_ARRAY_ELEMENTS(preferences->'tags') AS value
WHERE user_id = 2;

Common Patterns

Flexible Metadata Column

CREATE TABLE events (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    metadata   JSONB
);

-- Store different shapes per event type
INSERT INTO events (event_type, metadata) VALUES
('page_view',  '{"url": "/home", "user_agent": "Chrome", "duration_ms": 450}'),
('purchase',   '{"product_id": 42, "amount": 29.99, "currency": "USD"}'),
('error',      '{"code": 500, "message": "Internal error", "stack": "..."}');

-- Query specific event types efficiently
CREATE INDEX events_type_idx ON events (event_type);
CREATE INDEX events_metadata_gin ON events USING GIN (metadata);

SELECT metadata->>'url', metadata->>'duration_ms'
FROM events
WHERE event_type = 'page_view'
  AND (metadata->>'duration_ms')::INTEGER > 1000;