JSON columns

@Json() on a property of an entity stores it as JSON in one column: a class of the application, a list or a map of them, or plain Map<String, Object?> and List data.

Why

Some values belong to an aggregate and are never read alone: settings, an address list, the raw payload of a webhook. A table of their own would be work for nothing, and a column type of the database cannot hold them. A JSON column keeps the value whole, and the application still works with its own classes.

Example

@Table('profiles')
class UserProfile {
  @Id()
  int? id;

  @Json() // PostgreSQL: jsonb
  Settings settings; // a class of the application

  @Json(binary: false) // PostgreSQL: json
  Map<String, Object?> raw;

  @Json()
  List<Address>? addresses;
}

The value is written and read by the application's ObjectMapper: the bean controllers and Feign clients use, so its settings and the @Json… annotations of the classes apply. Without the bean, a mapper with the default settings is used. The build describes the classes of the property's type, nested ones too, the way it describes a controller's body: there is no @Introspected to add for the classes of the application's package.

A null property is a SQL NULL, not the JSON null.

What each store keeps

Store What it keeps
PostgreSQL a jsonb column, or json with binary: false (the text as written: key order, spaces); bound as text with CAST(? AS JSONB)
SQLite the JSON text in a TEXT column; a table the starter creates has CHECK (json_valid("column"))
any other SQL database the JSON text in a TEXT column
in memory the decoded JSON, copied on the way in and out
supabase (spring_boot_starter_supabase) the decoded JSON, sent and received as nested JSON

spring.data.jdbc.schema: create writes those column types and validate checks them (see Schema and migrations): PostgreSQL wants exactly jsonb or json, SQLite a text column. A JSON column gets no DEFAULT: the property's default value is used when a row has none.

A dialect of your own says how its database keeps JSON in one place: SqlDialect.jsonStorage(Json) returns a JsonStorage (the column type, the test of an existing column, the marker and the value a statement binds, the reading of what the driver gives).

Queries

A @Query reads such a column like the repository's own methods do, and it is the way to filter by what is inside:

@Query("select * from profiles where settings ->> 'theme' = :theme")
Future<List<UserProfile>> themed(String theme);

A derived query cannot filter or sort by a @Json property (findBySettings, OrderBySettings, a Sort by it): it is a build error, and an InvalidDataAccessApiUsageException at run time on every store. Use @Query with the database's JSON operators on a SQL store, or a database function.

What is refused

A Map or a List without @Json

A SQL database has no column for a Map, a List or a Set. On a SQL store such a property without @Json is refused before any statement runs, with the fix:

Invalid repository Parcels:
  - Parcel.meta: a Map<String, Object?> property without @Json has no column on a SQL store: add @Json() to keep it as JSON, or @MappedCollection for rows of a child table

What is not refused:

A store of your own asks for the build's check with DataStoreDescriptor(collectionsNeedJson: true), and reports its own problems of an entity when a repository is created by implementing EntityCheckingDataStore (see Stores and their capabilities). A dialect that has array columns overrides SqlDialect.storesCollection(type).

In Spring (Java)

@Table("profiles")
class UserProfile {
  @Id Long id;
  Settings settings; // needs a @WritingConverter and a @ReadingConverter to PGobject
}

Spring Data JDBC has no JSON annotation: the application registers a pair of converters for each class, to PostgreSQL's PGobject. Hibernate has @JdbcTypeCode(SqlTypes.JSON). A collection it cannot map fails at the first statement, not at start.

See also