
# 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

```dart
@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](../spring-boot-starter-supabase/data.md)) | 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](schema.md)): 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`](query.md) reads such a column like the repository's own methods do, and it is the way to filter by what is inside:

```dart
@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](database-functions.md).

## What is refused

- `@Json` on the `@Id`, the `@Version`, an audited property, a `@MappedCollection` or a reference to another aggregate: a `MappingException` when the entity is read.
- A stored value that cannot be read as the property's type: a `MappingException` naming 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`, or `spring.datasource.url` in the configuration): a `Map`, a `Set`, or a `List` of 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: validate` and `create` report the same line.

What is not refused:

- `List<int>` and `Uint8List`: they are bytes (`BLOB`, `BYTEA`).
- A [`@MappedCollection`](entities.md): its children are rows of a table of their own.
- On PostgreSQL, a `List` of 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. A `Set` and a `Map` are 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](stores.md)). A dialect that has array columns overrides `SqlDialect.storesCollection(type)`.

## In Spring (Java)

```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

- [Entities](entities.md)
- [@Query](query.md)
- [Schema and migrations](schema.md)
- [Stores and their capabilities](stores.md)
