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
@Jsonon the@Id, the@Version, an audited property, a@MappedCollectionor a reference to another aggregate: aMappingExceptionwhen the entity is read.- A stored value that cannot be read as the property's type: a
MappingExceptionnaming the class and the property, never the value.
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
- At build time, when the build knows that the repository's store is the SQL store (
@UseStore('jdbc'),@UseDataSource, orspring.datasource.urlin the configuration): aMap, aSet, or aListof anything but simple values is a build error. - When the application starts, on every SQL store: the repository is not created, and
spring.data.jdbc.schema: validateandcreatereport the same line.
What is not refused:
List<int>andUint8List: they are bytes (BLOB,BYTEA).- A
@MappedCollection: its children are rows of a table of their own. - On PostgreSQL, a
Listof simple values (List<String>,List<double>,List<bool>,List<DateTime>) in an array column you create (tags text[]): it is stored and read as before. The build leaves such a list to the store; SQLite, which has no arrays, refuses it at start. ASetand aMapare never arrays. - The in-memory store keeps the Dart object as it is, and a store that is not SQL decides for itself: neither is checked.
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.