advanced~3h

JSON Columns — PostgreSQL JSONB & MySQL JSON with Hibernate

Not every field has a fixed shape at design time — JSON columns are the deliberate escape hatch for genuinely variable, evolving data, now natively supported by Hibernate 6 without needing a third-party conversion library.

Learning objectives

Map a Java field to a native JSONB/JSON column using Hibernate 6's @JdbcTypeCode.
Query into nested JSON structure and explain why indexing it matters for performance.
Decide correctly when data belongs in a JSON column versus as proper relational columns.

Not every field has a fixed shape at design time — JSON columns are the deliberate escape hatch, now natively supported by Hibernate 6 without needing a third-party library.

📖 Story

Imagine trying to design one universal, fixed-column table for "product specifications" across a store selling both laptops (RAM, CPU, screen size) and t-shirts (size, color, fabric). Here's the rigid, painful version:

-- Every product needs columns for EVERY possible attribute across EVERY category:
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY, name TEXT, price NUMERIC,
    ram_gb INT,        -- NULL for every t-shirt
    cpu TEXT,          -- NULL for every t-shirt
    shirt_size TEXT,   -- NULL for every laptop
    fabric TEXT        -- NULL for every laptop
);

Here's the JSON column fix — flexible attributes, alongside the truly universal fields:

@Entity
public class Product {
    @Id @GeneratedValue
    private Long id;
    private String name;
    private BigDecimal price;

    @JdbcTypeCode(SqlTypes.JSON)
    private Map<String, Object> attributes;   // {"ram_gb": 16} OR {"size": "M", "fabric": "cotton"}
}

JSONB (PostgreSQL) — a binary, parsed, indexable JSON storage format. JSON (MySQL) — MySQL's native JSON column type. @JdbcTypeCode — the Hibernate 6 annotation mapping a Java field directly to a native JSON/JSONB column.

Let's finish this chapter's product-catalog example with querying and indexing.

Querying into the JSON structure (PostgreSQL)

@Query(value = "SELECT * FROM products WHERE attributes @> :filter::jsonb", nativeQuery = true)
List<Product> findByAttribute(@Param("filter") String jsonFilter);

// Called like: findByAttribute("{\"ram_gb\": 16}")
// Finds every laptop with 16GB RAM, without ever touching a rigid schema.

Indexing in PostgreSQL — don't skip this

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

Without this GIN index, that @> query above degrades to scanning and re-parsing every single row's JSON content. With it, PostgreSQL can index directly into the JSONB structure — a single index that supports containment queries against ANY key inside attributes, without you having to know in advance which keys you'd want to query.

MySQL's very different indexing story

MySQL's JSON type has no GIN-style equivalent — there's no way to index "the whole document" for arbitrary containment queries. Instead, you index one specific JSON path at a time, via a generated column: a real, regular column whose value MySQL derives automatically from a JSON path, which you can then put a normal B-Tree index on.

ALTER TABLE products
    ADD COLUMN ram_gb INT GENERATED ALWAYS AS (attributes->>'$.ram_gb') VIRTUAL,
    ADD INDEX idx_products_ram_gb (ram_gb);

This makes a real, practical difference to how you design around JSON columns in each database: PostgreSQL's GIN index means you don't have to predict which keys you'll query later; MySQL requires you to add one generated column (and one index) per JSON path you actually need to query efficiently, decided up front. Hibernate's @JdbcTypeCode(SqlTypes.JSON) maps to the correct native column type either way — the annotation on your entity doesn't change between the two databases, only the indexing strategy you layer on top of it does.

The core tradeoff, illustrated by this chapter's example

The attributes column gained real flexibility — a new attribute (say, battery_life_hours) needs zero migration. But the database enforces NOTHING about its internal shape — a typo'd key ("raam_gb" instead of "ram_gb") would silently store wrong data with no constraint ever catching it.

PostgreSQL's JSONB stores content in a parsed, binary internal representation, not the raw text you inserted — this is precisely what makes GIN indexing and @> containment queries efficient, unlike plain JSON, which stores exact text and must be re-parsed on every access. Hibernate's @JdbcTypeCode(SqlTypes.JSON) serializes your Java field (via Jackson) to a JSON string at write time, and deserializes it back at read time.

This chapter's exact multi-category product catalog (laptops, t-shirts, wildly different attributes) is a very common real-world JSON-column pattern.
A flexible user-settings blob, growing over time as new features ship, is a textbook JSON-column use case — the alternative (a new column per setting) would need a migration every time.

Use a JSON column specifically for genuinely variable, evolving data, like this chapter's product attributes — not as a general escape hatch for data that's actually stable.
On PostgreSQL, add a GIN index (this chapter's example) for any JSON field you query or filter on frequently. On MySQL, identify the specific paths you'll actually query and add a generated column + B-Tree index per path — there's no MySQL equivalent to "just index the whole document."
Add application-level validation for a JSON column's expected structure — the database enforces none.
Prefer @JdbcTypeCode(SqlTypes.JSON) (Hibernate 6's native support) over a third-party converter library for new code — it works correctly against both PostgreSQL and MySQL without any dialect-specific code on your entity.

⚠️ Why this keeps happening

A JSON column's flexibility genuinely tempts using it as a general escape hatch from schema design discipline.

Using a JSON column for data that's actually stable and well-known, losing straightforward querying and structural enforcement for no real benefit.
Never adding an index on a frequently-queried JSON field — exactly this chapter's warning about the un-indexed @> query degrading to a full scan.
Assuming the database enforces any structure, then being surprised when a typo'd key silently makes it into production data.

JSONB's binary storage is what makes indexed queries efficient — always prefer JSONB over plain JSON for any column you intend to query into. A GIN index (this chapter's example) can make containment queries nearly as efficient as an equivalent relational column, but only if it actually exists.

JSON content received from an untrusted client should be validated at the application boundary before storage — since the database enforces no structural constraint, an unvalidated write could store unexpected types or deeply nested structures.

Monitor query performance specifically for endpoints filtering on JSON fields, separately from normal relational-column monitoring — a missing JSON index is an easy-to-overlook performance gap.

Document the EXPECTED shape of any JSON column's content, even informally — it's the only real documentation of what it contains, since there's no database-level schema to inspect.

Build this chapter's exact Product entity with a JSONB attributes column using @JdbcTypeCode(SqlTypes.JSON), and confirm it correctly serializes/deserializes on save and load.
Write this chapter's findByAttribute native query using PostgreSQL's @> operator.
Add the GIN index from this chapter's example and compare query plans (via EXPLAIN) before and after.

✓ Quick recap

JSON columns (like this chapter's product attributes) let you store genuinely variable data, avoiding a migration for every new possible field.
Hibernate 6's @JdbcTypeCode(SqlTypes.JSON) provides native mapping, generally replacing third-party conversion libraries.
Always index a frequently-queried JSON field — this chapter's GIN index example — or queries degrade to a full scan.
Use JSON columns for variable/evolving data, not as a substitute for proper relational design of stable fields.

Want a visual for this concept?

Generate a diagram tailored to “JSON Columns — PostgreSQL JSONB & MySQL JSON with Hibernate” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Practice interview questions on this topic →← Back to all Spring Data JPA & Hibernate Mastery chapters