How to Use JSONB in PostgreSQL

Key Insights

JSONB stores JSON data in a decomposed binary format that enables efficient querying and indexing, making it significantly faster than the text-based JSON type for operations beyond simple storage and retrieval.
PostgreSQL provides specialized operators (->, ->>, @>, ?) and indexing strategies (GIN, GiST) that allow you to query JSONB columns almost as efficiently as traditional relational columns while maintaining schema flexibility.
Use JSONB for semi-structured data, API payloads, and evolving schemas, but stick with normalized tables when you need strict data integrity, complex joins, or when your data structure is stable and well-defined.

Introduction to JSONB

JSONB is PostgreSQL’s binary JSON storage format that combines the flexibility of document databases with the power of relational databases. Unlike the plain JSON type that stores data as text, JSONB decomposes JSON into a binary structure that supports indexing and efficient querying.

The key difference between JSON and JSONB is processing overhead versus query performance. JSON preserves the exact formatting of your input, including whitespace and key ordering, but requires parsing on every access. JSONB processes data once during insertion, removes insignificant whitespace, and doesn’t preserve key order—but subsequent operations are dramatically faster.

Use JSONB when you need to store semi-structured data that doesn’t fit neatly into relational columns, handle API responses with varying structures, or build applications where schema flexibility matters more than rigid constraints. Avoid it when you need foreign key relationships, complex multi-table joins, or strict type validation that SQL provides naturally.

Creating Tables and Inserting JSONB Data

Creating a table with JSONB columns is straightforward. Here’s a practical example for an e-commerce system storing product data:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    attributes JSONB NOT NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Inserting JSONB data accepts standard JSON syntax. PostgreSQL validates the JSON structure during insertion:

INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "TechCorp", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "tags": ["electronics", "computers"]}'),
('Headphones', '{"brand": "AudioPro", "specs": {"type": "wireless", "battery": "20 hours"}, "tags": ["electronics", "audio"]}');

For nested structures and arrays, JSONB handles arbitrary complexity:

INSERT INTO products (name, attributes) VALUES
('Smart Watch', '{
    "brand": "FitTech",
    "specs": {
        "display": "AMOLED",
        "sensors": ["heart_rate", "gps", "accelerometer"]
    },
    "compatibility": {
        "ios": true,
        "android": true,
        "min_versions": {"ios": "13.0", "android": "8.0"}
    },
    "tags": ["wearables", "fitness"]
}');

Querying JSONB Data

PostgreSQL provides multiple operators for extracting JSONB data. The -> operator returns JSONB, while ->> returns text:

-- Get brand as JSONB
SELECT name, attributes->'brand' as brand FROM products;

-- Get brand as text
SELECT name, attributes->>'brand' as brand FROM products;

-- Extract nested values
SELECT name, attributes->'specs'->>'ram' as ram FROM products;

For deeper nesting, use the path operators #> and #>>:

-- Navigate multiple levels (returns JSONB)
SELECT name, attributes#>'{specs,sensors}' as sensors FROM products;

-- Navigate and return text
SELECT name, attributes#>>'{compatibility,min_versions,ios}' as min_ios FROM products;

Filtering with WHERE clauses works seamlessly:

-- Find products by brand
SELECT name FROM products 
WHERE attributes->>'brand' = 'TechCorp';

-- Query nested values
SELECT name FROM products 
WHERE (attributes->'specs'->>'ram') = '16GB';

-- Query array elements
SELECT name FROM products 
WHERE attributes->'tags' @> '["electronics"]';

Indexing JSONB for Performance

Without indexes, JSONB queries perform sequential scans. GIN (Generalized Inverted Index) indexes dramatically improve performance for containment and existence queries:

-- Create a GIN index on the entire JSONB column
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- Now containment queries use the index
SELECT name FROM products 
WHERE attributes @> '{"brand": "TechCorp"}';

For queries targeting specific JSON paths, expression indexes are more efficient:

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

-- Index nested paths
CREATE INDEX idx_products_ram ON products ((attributes->'specs'->>'ram'));

-- Use the index
SELECT name FROM products WHERE attributes->>'brand' = 'AudioPro';

GIN indexes support multiple operator classes. The default jsonb_ops indexes all keys and values, while jsonb_path_ops only indexes values and uses less space:

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

The performance difference is substantial. On a table with 100,000 products, a GIN index can reduce query time from 200ms to under 5ms for containment queries.

Modifying JSONB Data

Updating JSONB data requires specific functions. The jsonb_set() function modifies values at a specified path:

-- Update a top-level key
UPDATE products 
SET attributes = jsonb_set(attributes, '{brand}', '"NewBrand"')
WHERE name = 'Laptop';

-- Update nested values
UPDATE products 
SET attributes = jsonb_set(attributes, '{specs,ram}', '"32GB"')
WHERE name = 'Laptop';

-- Add new keys
UPDATE products 
SET attributes = jsonb_set(attributes, '{warranty}', '"2 years"', true)
WHERE name = 'Laptop';

The || operator merges JSONB objects, with the right operand overwriting matching keys:

-- Add or update multiple keys
UPDATE products 
SET attributes = attributes || '{"color": "silver", "weight": "1.5kg"}'::jsonb
WHERE name = 'Laptop';

-- Merge nested objects
UPDATE products 
SET attributes = jsonb_set(
    attributes, 
    '{specs}', 
    (attributes->'specs') || '{"cpu": "Intel i7"}'::jsonb
)
WHERE name = 'Laptop';

Remove keys with the - operator:

-- Remove a single key
UPDATE products 
SET attributes = attributes - 'color'
WHERE name = 'Laptop';

-- Remove multiple keys
UPDATE products 
SET attributes = attributes - '{color,weight}'::text[]
WHERE name = 'Laptop';

-- Remove from nested objects
UPDATE products 
SET attributes = jsonb_set(
    attributes,
    '{specs}',
    (attributes->'specs') - 'cpu'
)
WHERE name = 'Laptop';

Advanced JSONB Operations

Containment operators check if one JSONB value contains another. The @> operator checks if the left side contains the right:

-- Find products with specific attributes
SELECT name FROM products 
WHERE attributes @> '{"brand": "TechCorp"}';

-- Check nested containment
SELECT name FROM products 
WHERE attributes @> '{"specs": {"ram": "16GB"}}';

-- Reverse containment with <@
SELECT name FROM products 
WHERE '{"brand": "TechCorp"}'::jsonb <@ attributes;

Key existence operators are useful for schema validation:

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

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

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

Aggregate JSONB data with jsonb_agg() and jsonb_object_agg():

-- Aggregate rows into a JSONB array
SELECT jsonb_agg(attributes->'brand') as brands FROM products;

-- Create JSONB object from rows
SELECT jsonb_object_agg(name, attributes->'brand') as product_brands 
FROM products;

-- Group and aggregate
SELECT 
    attributes->>'brand' as brand,
    jsonb_agg(name) as products
FROM products 
GROUP BY attributes->>'brand';

Array operations work naturally with JSONB arrays:

-- Expand JSONB arrays to rows
SELECT name, jsonb_array_elements_text(attributes->'tags') as tag 
FROM products;

-- Filter by array contents
SELECT name FROM products 
WHERE attributes->'tags' @> '["electronics"]';

-- Count array elements
SELECT name, jsonb_array_length(attributes->'tags') as tag_count 
FROM products;

Best Practices and Common Pitfalls

Choose JSONB over normalized tables when dealing with highly variable data structures, third-party API integrations, or rapid prototyping where schema changes are frequent. Stick with traditional columns when you need referential integrity, complex joins across multiple tables, or when your data structure is stable.

Don’t store everything in JSONB. Extract frequently queried fields into regular columns and use JSONB for supplementary data. This hybrid approach gives you the best of both worlds:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    brand VARCHAR(100) NOT NULL,  -- Frequently queried
    price DECIMAL(10,2) NOT NULL,  -- Used in calculations
    attributes JSONB,  -- Variable specs
    metadata JSONB  -- Rarely queried data
);

Index strategically. GIN indexes consume significant space—don’t index JSONB columns unless you actually query them. Use expression indexes for specific paths rather than indexing entire JSONB columns when possible.

Validate JSONB structure at the application layer or with CHECK constraints:

ALTER TABLE products 
ADD CONSTRAINT check_attributes_structure 
CHECK (attributes ? 'brand' AND attributes ? 'specs');

Avoid deeply nested structures. Three levels deep is usually the practical limit before queries become unwieldy. Consider flattening or splitting into multiple JSONB columns.

Remember that JSONB doesn’t enforce types within the JSON structure. The value at attributes->specs->ram could be a string, number, or even an object. Your application must handle this variability.

JSONB is a powerful tool that bridges relational and document databases, but it’s not a replacement for proper database design. Use it judiciously where flexibility genuinely adds value.