PostgreSQL JSONB: Store and Query JSON Like a Pro

PostgreSQL’s JSONB type gives you the flexibility of NoSQL with the reliability of a relational database. Store semi-structured data, query nested objects, and index for performance — all within PostgreSQL.

JSON vs JSONB

Feature	JSON	JSONB
Storage	Text (as-is)	Binary (parsed)
Duplicate Keys	Preserved	Last wins
Key Order	Preserved	Not guaranteed
Indexing	No	GIN, GiST
Operations	Limited	Full operators
Performance	Slower queries	Faster queries

Rule of thumb: Use JSONB unless you need to preserve exact JSON formatting.

Creating Tables with JSONB

-- User preferences as JSONB
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    profile JSONB DEFAULT '{}',
    settings JSONB DEFAULT '{
        "theme": "light",
        "notifications": true,
        "language": "en"
    }',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Product with flexible attributes
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    attributes JSONB NOT NULL DEFAULT '{}',
    tags JSONB DEFAULT '[]'
);

-- Insert examples
INSERT INTO users (email, profile, settings)
VALUES (
    'john@example.com',
    '{
        "firstName": "John",
        "lastName": "Doe",
        "age": 30,
        "address": {
            "city": "New York",
            "country": "USA"
        },
        "interests": ["coding", "music", "travel"]
    }',
    '{"theme": "dark", "notifications": false}'
);

INSERT INTO products (name, price, attributes, tags)
VALUES (
    'Gaming Laptop',
    1299.99,
    '{
        "brand": "TechPro",
        "specs": {
            "cpu": "Intel i9",
            "ram": "32GB",
            "storage": "1TB SSD",
            "gpu": "RTX 4080"
        },
        "warranty": 24,
        "inStock": true
    }',
    '["electronics", "computers", "gaming"]'
);

Querying JSONB Data

Accessing Values

-- Arrow operator (->) returns JSONB
SELECT profile->'firstName' FROM users;
-- Result: "John" (with quotes, as JSON)

-- Double arrow (->>) returns TEXT
SELECT profile->>'firstName' FROM users;
-- Result: John (without quotes, as text)

-- Nested access
SELECT profile->'address'->>'city' FROM users;
-- Result: New York

-- Array access (0-indexed)
SELECT profile->'interests'->>0 FROM users;
-- Result: coding

-- Path access with #> and #>>
SELECT profile#>'{address,city}' FROM users;
SELECT profile#>>'{address,city}' FROM users;

Filtering with JSONB

-- Exact match on nested value
SELECT * FROM products
WHERE attributes->>'brand' = 'TechPro';

-- Numeric comparison (cast to numeric)
SELECT * FROM products
WHERE (attributes->>'warranty')::int > 12;

-- Boolean check
SELECT * FROM products
WHERE (attributes->>'inStock')::boolean = true;

-- Containment operator (@>)
SELECT * FROM products
WHERE attributes @> '{"brand": "TechPro"}';

-- Check if key exists
SELECT * FROM products
WHERE attributes ? 'warranty';

-- Check if any key exists
SELECT * FROM products
WHERE attributes ?| array['warranty', 'guarantee'];

-- Check if all keys exist
SELECT * FROM products
WHERE attributes ?& array['brand', 'specs'];

Array Operations

-- Check if array contains element
SELECT * FROM users
WHERE profile->'interests' ? 'coding';

-- Containment with arrays
SELECT * FROM products
WHERE tags @> '["gaming"]';

-- Array overlap (&&)
SELECT * FROM products
WHERE tags ?| array['gaming', 'electronics'];

-- Get array length
SELECT jsonb_array_length(tags) FROM products;

-- Expand array elements
SELECT id, name, jsonb_array_elements_text(tags) as tag
FROM products;

Modifying JSONB Data

Update Operations

-- Set/update a key
UPDATE users
SET profile = jsonb_set(profile, '{age}', '31')
WHERE email = 'john@example.com';

-- Set nested key
UPDATE users
SET profile = jsonb_set(profile, '{address,zipCode}', '"10001"')
WHERE email = 'john@example.com';

-- Create path if doesn't exist
UPDATE products
SET attributes = jsonb_set(
    attributes,
    '{dimensions,weight}',
    '"2.5kg"',
    true  -- create_if_missing
)
WHERE name = 'Gaming Laptop';

-- Remove a key
UPDATE users
SET profile = profile - 'age'
WHERE email = 'john@example.com';

-- Remove nested key
UPDATE users
SET profile = profile #- '{address,zipCode}'
WHERE email = 'john@example.com';

-- Merge/concatenate JSONB (||)
UPDATE users
SET settings = settings || '{"fontSize": 14, "compact": true}'
WHERE email = 'john@example.com';

Array Modifications

-- Append to array
UPDATE users
SET profile = jsonb_set(
    profile,
    '{interests}',
    profile->'interests' || '"photography"'
)
WHERE email = 'john@example.com';

-- Remove from array (by index)
UPDATE products
SET tags = tags - 1  -- removes second element
WHERE name = 'Gaming Laptop';

-- Remove from array (by value, requires subquery)
UPDATE users
SET profile = jsonb_set(
    profile,
    '{interests}',
    (
        SELECT jsonb_agg(elem)
        FROM jsonb_array_elements(profile->'interests') elem
        WHERE elem::text != '"travel"'
    )
)
WHERE email = 'john@example.com';

Indexing JSONB

GIN Index for Containment

-- Index entire JSONB column
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);

-- Now these queries use the index:
SELECT * FROM products WHERE attributes @> '{"brand": "TechPro"}';
SELECT * FROM products WHERE attributes ? 'warranty';

-- For specific paths (more efficient)
CREATE INDEX idx_products_brand ON products USING GIN((attributes->'specs'));

Expression Indexes

-- Index specific JSON path
CREATE INDEX idx_products_brand ON products((attributes->>'brand'));

-- Query that uses this index
SELECT * FROM products WHERE attributes->>'brand' = 'TechPro';

-- Functional index on array length
CREATE INDEX idx_products_tags_count ON products(jsonb_array_length(tags));

-- Index on cast value
CREATE INDEX idx_products_warranty ON products(((attributes->>'warranty')::int));

GIN with jsonb_path_ops

-- Smaller index, only supports @> operator
CREATE INDEX idx_products_attrs_path ON products
USING GIN(attributes jsonb_path_ops);

-- More efficient for deep containment queries
SELECT * FROM products
WHERE attributes @> '{"specs": {"gpu": "RTX 4080"}}';

Advanced Queries

Aggregations

-- Group by JSONB field
SELECT
    attributes->>'brand' as brand,
    COUNT(*) as count,
    AVG(price) as avg_price
FROM products
GROUP BY attributes->>'brand';

-- Build JSONB from aggregation
SELECT jsonb_object_agg(
    attributes->>'brand',
    COUNT(*)
) as brands_count
FROM products
GROUP BY attributes->>'brand';

JSONB Functions

-- Get all keys
SELECT jsonb_object_keys(attributes) FROM products;

-- Expand to rows
SELECT * FROM jsonb_each(
    '{"a": 1, "b": 2, "c": 3}'::jsonb
);
-- Returns: key | value

-- Pretty print
SELECT jsonb_pretty(attributes) FROM products;

-- Build JSONB from values
SELECT jsonb_build_object(
    'name', name,
    'price', price,
    'brand', attributes->>'brand'
) as product_summary
FROM products;

-- Build array from query
SELECT jsonb_agg(
    jsonb_build_object('id', id, 'name', name)
) as products_json
FROM products
WHERE price < 1000;

JSON Path Queries (PostgreSQL 12+)

-- JSONPath syntax
SELECT * FROM products
WHERE jsonb_path_exists(
    attributes,
    '$.specs.ram ? (@ == "32GB")'
);

-- Extract with path
SELECT jsonb_path_query(
    attributes,
    '$.specs.*'
) as spec_values
FROM products;

-- Filter with path
SELECT jsonb_path_query_array(
    profile,
    '$.interests[*] ? (@ starts with "c")'
) as c_interests
FROM users;

-- Path with variables
SELECT * FROM products
WHERE jsonb_path_exists(
    attributes,
    '$.warranty ? (@ > $min)',
    '{"min": 12}'
);

Schema Validation with CHECK

-- Validate JSONB structure
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    CONSTRAINT valid_order CHECK (
        data ? 'customer_id' AND
        data ? 'items' AND
        jsonb_typeof(data->'items') = 'array' AND
        jsonb_array_length(data->'items') > 0
    )
);

-- More complex validation
ALTER TABLE products ADD CONSTRAINT valid_attributes CHECK (
    attributes ? 'brand' AND
    (attributes->>'brand') IS NOT NULL AND
    length(attributes->>'brand') >= 2
);

Performance Tips

1. Use Specific Indexes

-- ❌ Generic GIN (larger, slower updates)
CREATE INDEX idx_data ON orders USING GIN(data);

-- ✅ Specific expression index (smaller, faster)
CREATE INDEX idx_customer ON orders((data->>'customer_id'));
CREATE INDEX idx_status ON orders((data->>'status'));

2. Avoid SELECT *

-- ❌ Returns entire JSONB
SELECT * FROM products;

-- ✅ Extract only what you need
SELECT
    id,
    name,
    attributes->>'brand' as brand,
    attributes->'specs'->>'cpu' as cpu
FROM products;

3. Denormalize Hot Paths

-- Add computed column for frequently accessed data
ALTER TABLE products ADD COLUMN brand VARCHAR(100)
    GENERATED ALWAYS AS (attributes->>'brand') STORED;

CREATE INDEX idx_products_brand_text ON products(brand);

4. Use JSONB for Flexibility, Columns for Speed

-- Hybrid approach: columns for indexed/frequent fields
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,  -- Indexed, frequently queried
    user_id INTEGER NOT NULL,          -- Indexed, foreign key
    timestamp TIMESTAMP NOT NULL,      -- Indexed, for time queries
    metadata JSONB DEFAULT '{}'        -- Flexible extra data
);

Conclusion

PostgreSQL JSONB gives you:

• Flexibility: Schema-less data alongside structured columns
• Performance: GIN indexes for fast queries
• Rich operators: Containment, existence, path queries
• SQL integration: Join, aggregate, filter like regular columns

Use JSONB for metadata, settings, and semi-structured data while keeping core fields as regular columns for optimal performance.

---

Working with JSONB in production? Share your tips in the comments!