Using jsonb for Flexible Data

You've modeled your data with rigid columns, and now a new product requirement arrives — you need to store a field that changes shape between customers, events, or API calls. Migrating the schema, backfilling rows, and coordinating releases across services can add days to an otherwise simple feature. PostgreSQL's jsonb data type cuts through this: store arbitrary, well-typed JSON directly in a column, query deep into it with SQL, and index what matters. This lesson teaches you to use jsonb for flexible data models without losing PostgreSQL's power, so you can adapt to changing requirements without a migration every time.

The problem this lesson solves

Traditional relational modeling requires a fixed shape. When your data doesn't fit a neat grid — be it user preferences, event payloads, or integration metadata — you're forced into awkward designs: wide tables with hundreds of mostly null columns, entity-attribute-value (EAV) schemas that are painful to query, or serializing blobs that are opaque to the database.

Here's what happens when you avoid flexibility:

• Schema migrations become frequent and risky. Every new optional field means an ALTER TABLE and a backfill.
• Queries get slow or ugly. With EAV, you join multiple times just to fetch one record's attributes.
• Code and schema drift. Your application accepts JSON, but the database forces you to shred it into columns.

jsonb changes the trade-off. It gives you a schema-less container inside a relational database. You keep ACID compliance, SQL joins, and vacuuming, but you gain the ability to store nested, varying data and query it with rich operators such as ->, ->>, and @>.

By the end of this lesson, you'll understand the core idea behind using jsonb for flexible data models, and you'll complete a practical exercise that you can apply immediately.

Core concept / mental model

Think of a normal table as a spreadsheet — every row has the same columns. Think of jsonb as a document — each row can contain a nested, self-describing structure. The magic is that the document lives inside a spreadsheet cell.

With jsonb, you model the intersection of two worlds:

• Relational for what's stable and query-hungry (ID, timestamp, owner).
• Flexible for what varies or evolves (settings, metadata, external API responses).

Here's a simple mental image:

+------------------+--------------------------------------------------+
|  product_id (int)|  attributes (jsonb)                               |
+------------------+--------------------------------------------------+
|  1               | {"color": "red", "size": "M", "tags": ["sale"]}      |
|  2               | {"weight": 1.5, "country": "DE", "warranty": 2}    |
+------------------+--------------------------------------------------+

The attributes column has no predefined keys — each row can hold a different structure. But you can still index into it, filter on it, and even create a GIN index for fast containment searches.

Why jsonb (not json)?

PostgreSQL offers two JSON types. The difference is critical:

Pro tip: Always choose jsonb unless you need to preserve key order or exact whitespace from the original JSON document. jsonb normalizes and reorders keys, which usually doesn't matter for application logic.

How it works step by step

Let's break down how to use jsonb effectively, from column creation to querying.

1. Create a table with a jsonb column

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    event_type TEXT NOT NULL,
    payload JSONB NOT NULL
);

The payload column can hold any valid JSON — an array, an object, a scalar. No constraints on shape unless you add a CHECK constraint.

2. Insert JSON data

You can insert a string literal, but you'll often pass a parameter from your application. Always use jsonb type in your driver (e.g., psycopg2 will adapt dictionaries to jsonb by default, but be explicit to avoid surprises).

INSERT INTO events (event_type, payload) VALUES
('page_view', '{"page": "/home", "user_id": 42, "utm": {"source": "newsletter"}}'),
('purchase',   '{"items": [{"sku": "A1", "qty": 2}], "total": 49.90}');

If you have a JSON string in a regular text column, you can cast: payload::jsonb.

3. Query into the JSON

Use the arrow operators:

• -> returns a JSONB value (works with objects and arrays).
• ->> returns as text.

SELECT event_type,
       payload->>'page' AS page,
       payload->'utm'->>'source' AS utm_source
FROM events
WHERE event_type = 'page_view';

Expected output:

 event_type |  page   | utm_source
------------+---------+------------
 page_view  | /home   | newsletter

4. Filter on nested values

SELECT id
FROM events
WHERE payload @> '{"utm": {"source": "newsletter"}}';

The @> operator checks if the left jsonb contains the right — useful for exact subdocument matching.

5. Index for speed

Without an index, a @> query scans the whole table. Create a GIN index to make it fast:

CREATE INDEX idx_events_payload_gin ON events USING gin (payload);

Now containment queries (@>, ?, ?|, ?&) and existence queries (?) can use the index.

Hands-on walkthrough

Let's build a small example from scratch. We'll create a product_attributes table for an e-commerce platform where each product type has different fields.

-- Create the table
CREATE TABLE product (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- Insert varied rows
INSERT INTO product (name, attributes) VALUES
('Running Shoes', '{"color": "blue", "sizes": [9, 10, 11], "material": "mesh"}'),
('Wireless Mouse', '{"dpi": 1600, "buttons": 5, "wireless": true}'),
('Yoga Mat', '{"thickness_mm": 6, "non_slip": true, "carry_strap": false}');

-- Query products with a 'wireless' key set to true
SELECT name
FROM product
WHERE attributes @> '{"wireless": true}';

-- Extract nested text
SELECT name, attributes->>'color' AS color
FROM product
WHERE attributes ? 'color';

-- Update one attribute without touching others
UPDATE product
SET attributes = jsonb_set(attributes, '{material}', '"knit"')
WHERE name = 'Running Shoes';

Expected output for the SELECTs:

 name
------------
 Wireless Mouse

    name      | color
--------------+-------
 Running Shoes| blue

The jsonb_set function is your friend for targeted updates — it avoids rewriting the whole document from your application code.

Working with arrays and existence

Sometimes you need to check if a key exists, or search inside an array of objects. Here's how:

-- Does any product have a "sizes" key?
SELECT name FROM product WHERE attributes ? 'sizes';

-- Select products where the sizes array contains 10
SELECT name
FROM product
WHERE attributes->'sizes' @> '10';

You can also unnest JSON arrays into rows for aggregation:

SELECT p.name, size
FROM product p, jsonb_array_elements_text(p.attributes->'sizes') AS size;

Compare options / when to choose what

You don't need jsonb for everything. Here's a comparison of the common approaches for flexible data:

Practical rule of thumb:

• If the attribute is always present and you query it frequently, make it a real column.
• If the attribute is optional, varies by type, or comes from an external API, put it in jsonb.
• If you need a constraint, you can add a CHECK on a jsonb expression, but it's not as natural as a column constraint.

Common variations

• jsonb with a generated column: You can create a generated column that extracts a common field, then index that column for fast filters.
• jsonb with a check constraint: Enforce that certain keys exist or have a specific type.
• Using jsonb in a normalized way: Keep stable attributes in columns, and add a jsonb column for the 'tail' of varying fields.

All these are valid, but the last one is probably the most balanced for production apps.

Troubleshooting & edge cases

1. jsonb key order and duplicate keys

jsonb does not preserve key order, and duplicate keys collapse to the last one. If you need exact fidelity, use json (not jsonb), or normalise your input.

2. Query that returns NULL unexpectedly

If payload->>'page' returns NULL, the key may not exist, or it exists with the value null. Use ? to test existence first:

SELECT event_type,
       CASE WHEN payload ? 'page' THEN payload->>'page' ELSE 'missing' END
FROM events;

3. Index not used

If your query uses payload->>'color' = 'blue', a GIN index won't help unless you create an expression index:

CREATE INDEX idx_product_color ON product ((attributes->>'color'));

Use EXPLAIN to confirm the index is being used.

4. JSON number precision

jsonb stores numbers as numeric, but if you cast to integer or float, you can lose precision. Be explicit:

SELECT (payload->>'total')::numeric FROM events;

5. Casting between json and jsonb

Casting json to jsonb loses key order and duplicate keys — plan your exports accordingly.

What you learned & what's next

You now understand the problem of rigid schemas, the core idea of using jsonb for flexible data models, and you've completed a hands-on exercise that shows inserting, querying, updating, and indexing JSON documents. You also learned when to choose jsonb over other approaches and how to avoid the most common pitfalls.

Next lesson: You'll learn how to combine JSON with relational data — using jsonb to store related records and how to handle transactional consistency when mixing the two. That will complete your toolkit for pragmatic schema design.

Keys takeaways to remember:

• jsonb offers flexible, queryable JSON storage inside a relational engine — perfect for evolving schemas.
• Use GIN indexes for containment @> queries, and expression indexes for frequent key extraction.
• Update specific keys with jsonb_set rather than rewriting the whole document.
• Keep stable attributes as columns and varying attributes in jsonb for optimal performance and maintainability.