JSON vs JSONB — Data Types, Storage, and When to Use Each

PostgreSQL has two JSON data types: json and jsonb. They store the same documents but differ fundamentally in how they work internally — and the difference determines everything about performance, indexing, and what you can do with the data.

The key difference

The practical rule: use jsonb for almost everything. The only reason to use json is when you need to preserve exact whitespace, key order, or duplicate keys — extremely rare requirements. For querying, indexing, and analytics, jsonb is strictly better.

Example 1: Creating tables with JSONB columns

The most common use case for JSONB in analytics: storing flexible metadata alongside structured columns. A product catalogue where different product types have different attributes, or an events table where each event type has a different payload structure.

Practice table: product_catalog is set up in the Practice Datasets — Overview lesson. If you haven't run that setup yet, go there first and run the "Additional Practice Tables" block.

Example 2: Basic JSONB operators

PostgreSQL's JSONB operators are the core of JSON querying. Learn these six and you can query almost any JSON structure:

-- The six essential JSONB operators:

-- -> returns JSONB (preserves type for nested access)
-- ->> returns TEXT (for leaf values you want to compare as strings)
-- #> path operator returning JSONB
-- #>> path operator returning TEXT
-- @> contains (does left document contain right document?)
-- ? key existence

-- Navigate to a top-level key as JSONB
SELECT name, attributes -> 'brand' AS brand_json
FROM product_catalog;

-- Navigate to a top-level key as TEXT (useful for comparisons)
SELECT name, attributes ->> 'brand' AS brand_text
FROM product_catalog WHERE attributes ->> 'brand' = 'Apple';

-- Access nested path: attributes -> 'specs' -> 'cpu'
SELECT name, attributes #>> '{specs,cpu}' AS cpu
FROM product_catalog WHERE category = 'Electronics';

-- Containment: does attributes contain this sub-document?
SELECT name FROM product_catalog
WHERE attributes @> '{"brand": "Apple"}';

-- Existence: does attributes have the key 'author'?
SELECT name, category FROM product_catalog
WHERE attributes ? 'author';

-- Multiple key existence: does it have 'brand' AND 'ram_gb'?
SELECT name FROM product_catalog
WHERE attributes ?& ARRAY['brand', 'ram_gb'];

-- Any key existence: does it have 'author' OR 'pages'?
SELECT name FROM product_catalog
WHERE attributes ?| ARRAY['author', 'pages'];

What These Return

The -> vs ->> distinction trips up almost everyone: use -> when you need to chain operators (accessing nested keys), use ->> when you want a text value to compare or display.

Example 3: Type extraction and casting from JSONB

JSONB stores all values as JSON types (strings, numbers, booleans, arrays, objects). When you extract them with ->>, you always get TEXT. To use them as numbers for calculations, you must cast:

SELECT
    name,
    category,
    base_price,
    -- Extract as text first, then cast to numeric
    (attributes ->> 'ram_gb')::INT AS ram_gb,
    (attributes ->> 'storage_gb')::INT AS storage_gb,
    (attributes ->> 'weight_kg')::NUMERIC AS weight_kg,
    -- Computed column from JSONB + base column
    ROUND(base_price / NULLIF((attributes ->> 'storage_gb')::INT, 0), 2) AS price_per_gb,
    -- Conditional extraction: only for electronics
    CASE WHEN category = 'Electronics'
        THEN (attributes ->> 'ram_gb')::INT
        ELSE NULL END AS ram_gb_electronics_only
FROM product_catalog
WHERE category = 'Electronics'
ORDER BY (attributes ->> 'ram_gb')::INT DESC;

The cast pattern (jsonb_column ->> 'key')::TYPE is standard PostgreSQL. Always check for NULL before casting — if the key doesn't exist, ->> returns NULL and the cast will succeed (NULL remains NULL), but if the key exists with a non-numeric value, the cast will error.

Example 4: JSONB functions for introspection

When working with unknown or variable JSON structures, the JSONB functions for introspecting the document shape are essential:

-- jsonb_object_keys: list all top-level keys
SELECT DISTINCT jsonb_object_keys(attributes) AS key
FROM product_catalog
ORDER BY key;

-- jsonb_each: expand top-level key/value pairs into rows
SELECT name, key, value
FROM product_catalog, jsonb_each(attributes) AS kv(key, value)
ORDER BY name, key;

-- jsonb_typeof: check the type of a JSON value
SELECT
    name,
    jsonb_typeof(attributes -> 'sizes') AS sizes_type,      -- 'array' or 'null'
    jsonb_typeof(attributes -> 'ram_gb') AS ram_type,       -- 'number'
    jsonb_typeof(attributes -> 'brand') AS brand_type       -- 'string'
FROM product_catalog;

-- jsonb_array_length: count elements in a JSONB array
SELECT
    name,
    attributes -> 'sizes' AS sizes_array,
    jsonb_array_length(attributes -> 'sizes') AS num_sizes
FROM product_catalog
WHERE attributes ? 'sizes';

-- Row count per key occurrence (which keys are most common?)
SELECT key, COUNT(*) AS occurrences
FROM product_catalog, jsonb_object_keys(attributes) AS key
GROUP BY key
ORDER BY occurrences DESC;

Key Takeaway

Use jsonb not json for every new column. The three-way operator split — -> (JSONB navigation), ->> (text extraction), @> (containment) — covers 90% of all JSON queries. Always cast ->> results to the appropriate type for arithmetic. When the document structure is unknown, jsonb_object_keys and jsonb_each let you explore it dynamically.