PostgreSQL JSONB with Spring Boot: Handle Custom Fields Without MongoDB

Why Not Add Columns, Use EAV, or Switch to MongoDB

A real-world scenario: a core customer table with 20+ base fields grew to nearly 80 extension columns, most NULL year-round. Adding physical columns requires DDL locks that DBAs reject. EAV (entity-attribute-value) tables make writes easy but queries a nightmare — joining dozens of rows to reconstruct one object, complex filtering via subqueries or pivoting. MongoDB offers flexible documents, but splitting orders, customers, and reconciliation across PostgreSQL and MongoDB introduces distributed transaction and sync headaches.

PostgreSQL jsonb lets you keep stable core columns (id, name, category_id, created_at) while stuffing unpredictable extension attributes into a single jsonb column. You retain transactions, joins, and reporting, and can still index JSONB internals. It's not a MongoDB replacement, but for "core relations stable, only extensions change" scenarios, it's the lowest total-cost choice. If your product is document-centric with almost no relational queries, MongoDB remains more natural. JSONB also doesn't solve large-scale aggregation across entire JSONB documents — explicit columns are better there.

JSONB Storage and Indexing Basics

PostgreSQL has json (text storage) and jsonb (parsed binary). jsonb deduplicates keys and reorders internally, so key order isn't preserved. Always choose jsonb.

Example table:

create table product (
  id bigint generated by default as identity primary key,
  name varchar(200) not null,
  category_id bigint not null references category(id),
  attrs jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

insert into product (name, category_id, attrs)
values ('智能手表', 1,
  '{"color": "黑色", "battery": {"capacity": 300, "unit": "mAh"}, "tags": ["智能", "运动"]}'::jsonb);

Reading:

-- extract field as text
select attrs->>'color' from product where id = 1;
-- extract nested object
select attrs->'battery' from product where id = 1;
-- array length
select jsonb_array_length(attrs->'tags') from product where id = 1;

For fast queries, use a GIN index. Two operator classes: gin(attrs) (default) supports @>, ?, ?|, ?& — can test key existence. gin(attrs jsonb_path_ops) optimizes attrs @> '{"key": "value"}' containment queries, smaller index, but cannot do key-existence checks like attrs ? 'color'.

Choose jsonb_path_ops if queries are mainly "attribute equals value"; use default GIN if you also need "does this key exist?". GIN isn't a universal key — not all JSONB queries benefit.

Spring Boot JPA/Hibernate JSONB Mapping

Spring Boot 3 (Hibernate 6) has built-in JSON mapping — no custom UserType needed.

@Entity
@Table(name = "product")
public class Product {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  private String name;
  @Column(name = "category_id")
  private Long categoryId;
  @JdbcTypeCode(SqlTypes.JSON)
  @Column(columnDefinition = "jsonb")
  private ProductAttrs attrs;
  // getters/setters omitted
}

ProductAttrs

is a plain POJO:

public class ProductAttrs {
  private String color;
  private Battery battery;
  private List<String> tags;
  // getters/setters omitted
}

Hibernate serializes the POJO to JSON on write, deserializes on read. Jackson is already on the classpath. For highly dynamic schemas, Map<String, Object> works but leads to pervasive casting — prefer defined structures unless truly dynamic. If still on Spring Boot 2.x / Hibernate 5, upgrade to Boot 3; old versions require manual UserType implementations not worth learning.

Updating JSONB: Prefer Atomic SQL Over Read-Modify-Write

JPA's default updates the entire JSONB column: read entity, modify in Java, write back. Two problems:

Concurrency: two threads read same attrs, modify different keys, last write overwrites the other's change.

Large documents: full rewrite is wasteful.

Use MyBatis or JdbcTemplate with PostgreSQL's jsonb_set for partial updates.

MyBatis TypeHandler for jsonb:

public class JsonbTypeHandler extends BaseTypeHandler<Object> {
  private final ObjectMapper objectMapper = new ObjectMapper();
  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
    try {
      PGobject jsonb = new PGobject();
      jsonb.setType("jsonb");
      jsonb.setValue(objectMapper.writeValueAsString(parameter));
      ps.setObject(i, jsonb);
    } catch (JsonProcessingException e) {
      throw new SQLException("can not convert to jsonb", e);
    }
  }
  // getNullableResult overloads deserialize PGobject.getValue()
}

Insert:

<insert id="insertProduct" parameterType="Product" useGeneratedKeys="true" keyProperty="id">
  insert into product(name, category_id, attrs)
  values (#{name}, #{categoryId}, #{attrs, jdbcType=OTHER, typeHandler=JsonbTypeHandler})
</insert>

Or serialize to JSON string in service layer and use #{jsonString}::jsonb.

Partial update with jsonb_set:

update product
set attrs = jsonb_set(attrs, '{color}', '"红色"'::jsonb, true)
where id = #{id};

Second argument is a text[] path array. In MyBatis, pass path as {battery, capacity} (not 'battery.capacity'). Third argument must be valid jsonb — for a string value, pass JSON-encoded string with quotes: objectMapper.writeValueAsString("红色") yields "红色". Without quotes, PostgreSQL treats it as an identifier and errors.

Top-level merge with ||:

update product
set attrs = attrs || '{"stockUnit": "台"}'::jsonb
where id = #{id};

Delete key: attrs - 'color' (top-level) or attrs #- '{battery, capacity}' (nested).

Caveat: || is shallow merge — it replaces entire nested objects, not deep-merge. Example:

'{"battery": {"price": 1}}'::jsonb || '{"battery": {"brand": "xx"}}'::jsonb

yields {"battery": {"brand": "xx"}}, losing price. Use jsonb_set for precise path updates.

Practical JSONB Query Patterns

Most common: "attribute equals value" using @> containment:

select * from product where attrs @> '{"color": "黑色"}';

In MyBatis, pass JSON filter string: where attrs @> #{jsonFilter}::jsonb.

Key existence ( attrs ? 'color') works only with default GIN, not jsonb_path_ops.

Array element search: avoid attrs->'tags' ? '智能' (GIN often can't use it). Prefer containment: attrs @> '{"tags": ["智能"]}' — expresses "tags array contains '智能'" and uses GIN effectively.

Nested path value: attrs #>> '{battery, capacity}' returns text. For numeric comparison, cast: (attrs #>> '{battery, capacity}')::numeric > 300. Path queries don't use GIN directly. For high-frequency path queries, create expression index:

create index idx_product_battery_capacity
on product (((attrs #>> '{battery, capacity}')::numeric));

Query must match index expression exactly to hit it.

For frequent sorting on a JSONB field (e.g., price), a B-tree expression index beats GIN:

create index idx_product_price on product (((attrs->>'price')::numeric));

select id, name, attrs->>'price' as price
from product
where attrs @> '{"brand": "Apple"}'
  and (attrs->>'price')::numeric between 100 and 500
order by (attrs->>'price')::numeric desc
limit 20;

Mixing Relational Columns and JSONB: Do's and Don'ts

Never put foreign keys ( category_id, owner_id) inside JSONB — they need joins, constraints, statistics. JSONB only for:

Future attributes that won't participate in relational constraints.

Historical snapshots.

If data is "master data" referenced elsewhere, make it a proper column or join table. Don't stuff relation IDs into JSONB arrays.

JSONB and regular columns share row-level locks. A transaction updating both regular columns and JSONB commits atomically. But PostgreSQL locks the whole row, not individual JSONB keys. Two transactions updating different keys on the same row serialize — safe from lost updates. The real danger is application-level "read whole entity, modify in memory, write back". Use select ... for update or atomic jsonb_set SQL instead.

Indexing and Performance Tuning Lessons

GIN isn't the only index, nor required for all JSONB queries. gin(attrs jsonb_path_ops) excels at @> containment but useless for range queries on a key — use B-tree expression index instead. attrs ? 'color' needs default GIN; jsonb_path_ops ignores it.

Anti-pattern: attrs::text like '%黑色%' — never uses indexes. If fuzzy search needed, target a specific key with pg_trgm expression index, but better avoid in design.

Data type stability matters: same key storing number 300 today and string "300" tomorrow breaks expression indexes and deserialization.

Real test on ~5M-row product table: attrs @> '{"brand": "Apple"}' took ~1200ms without index; with gin(attrs jsonb_path_ops) dropped to milliseconds. But if a value appears in hundreds of thousands of rows (high cardinality), GIN entry becomes dense and less effective — extract that high-frequency key to a regular column with a normal index.

Deep pagination with JSONB sorting: avoid offset. Use cursor pagination with last-seen price and id:

select id, name, attrs->>'price' as price
from product
where attrs @> '{"brand": "Apple"}'
  and (attrs->>'price')::numeric < #{lastPrice}
order by (attrs->>'price')::numeric desc
limit 20;

If prices duplicate, include id in cursor to avoid missing rows.

JSONB Is Not a MongoDB Drop-In Replacement

JSONB's value is combining "stable relational core" with "volatile extensions" in one PostgreSQL instance. Orders keep relational columns; custom promotions, logistics notes, buyer tags go into JSONB — no schema changes, still queryable via SQL.

Designers must distinguish: which fields will be queried (constrain types, add expression indexes) vs. which are archival only (don't build complex queries). The worst outcome is using a slightly convenient tool everywhere, turning the data model into mush. Next time someone asks "Should we add MongoDB for custom fields?", check if they're on PostgreSQL — a jsonb column may save a mountain of future trouble.