JSON

Every database supports JSON -- PostgreSQL has json/jsonb, MySQL and MariaDB have JSON, DuckDB has JSON, SQL Server has FOR JSON, and Oracle has its own JSON type. Your RowCodec doubles as a JSON codec with no extra code.

JSON-encoded column types

Pass a RowCodec to your database's jsonArrayEncoded or jsonObjectEncoded method to get a column type that reads and writes structured rows as JSON. An unnamed codec can produce positional JSON arrays ([value1, value2, ...]), while a named codec can also produce keyed JSON objects ({"column": value, ...}):

Kotlin

data class OrderLine(val product: String, val qty: Int, val price: BigDecimal)

val lineCodec: RowCodecNamed<OrderLine> =
    RowCodec.namedBuilder<OrderLine>()
        .field("product", DuckDbTypes.varchar, OrderLine::product)
        .field("qty", DuckDbTypes.integer, OrderLine::qty)
        .field("price", DuckDbTypes.decimalOf(10, 2), OrderLine::price)
        .build(::OrderLine)

// Stores rows as positional JSON arrays: [["Widget", 3, 9.99], ...]
val arrayType: DuckDbType<List<OrderLine>> =
    DuckDbTypes.jsonArrayEncodedList(lineCodec)

// Stores rows as named JSON objects: [{"product": "Widget", "qty": 3, "price": 9.99}, ...]
// Column names come from the codec — no redundant list to maintain
val objectType: DuckDbType<List<OrderLine>> =
    DuckDbTypes.jsonObjectEncodedList(lineCodec)

Java

record OrderLine(String product, int qty, BigDecimal price) {}

static final RowCodecNamed<OrderLine> lineCodec =
    RowCodec.<OrderLine>namedBuilder()
        .field("product", DuckDbTypes.varchar, OrderLine::product)
        .field("qty", DuckDbTypes.integer, OrderLine::qty)
        .field("price", DuckDbTypes.decimalOf(10, 2), OrderLine::price)
        .build(OrderLine::new);

// Stores rows as positional JSON arrays: [["Widget", 3, 9.99], ...]
static final DuckDbType<List<OrderLine>> arrayType = DuckDbTypes.jsonArrayEncodedList(lineCodec);

// Stores rows as named JSON objects: [{"product": "Widget", "qty": 3, "price": 9.99}, ...]
// Column names come from the codec — no redundant list to maintain
static final DuckDbType<List<OrderLine>> objectType =
    DuckDbTypes.jsonObjectEncodedList(lineCodec);

Scala

case class OrderLine(
    product: String,
    qty: Int,
    price: BigDecimal
)

val lineCodec: RowCodecNamed[OrderLine] =
  RowCodec
    .namedBuilder[OrderLine]()
    .field("product", DuckDbTypes.varchar)(_.product)
    .field("qty", DuckDbTypes.integer)(_.qty)
    .field("price", DuckDbTypes.decimalOf(10, 2))(_.price)
    .build(OrderLine.apply)

// Stores rows as positional JSON arrays
val arrayType: DuckDbType[List[OrderLine]] =
  DuckDbTypes.jsonArrayEncodedList(lineCodec)

// Stores rows as named JSON objects — keys from the codec
val objectType: DuckDbType[List[OrderLine]] =
  DuckDbTypes.jsonObjectEncodedList(lineCodec)

Show entire file

Every database type palette has four methods:

Method	Codec	JSON shape	Use case
jsonObjectEncoded(namedCodec)	DbType<Row>	{"col": val, ...}	Single row as keyed object
jsonArrayEncoded(codec)	DbType<Row>	[val, val, ...]	Single row as positional array
jsonObjectEncodedList(namedCodec)	DbType<List<Row>>	[{"col": val}, ...]	List of rows as object array
jsonArrayEncodedList(codec)	DbType<List<Row>>	[[val, val], ...]	List of rows as nested arrays

Available on PgTypes, MariaTypes, DuckDbTypes, SqlServerTypes, OracleTypes, and Db2Types. PostgreSQL also has jsonb variants (jsonbObjectEncoded, etc.).

Aggregating child rows as JSON

JSON-encoded types are most useful when aggregating child rows in SQL. Instead of N+1 queries, use your database's JSON aggregation function and parse the result with the same type:

Database	Aggregation function
PostgreSQL	json_agg() / jsonb_agg()
MariaDB/MySQL	JSON_ARRAYAGG()
DuckDB	json_group_array()
SQL Server	FOR JSON PATH
Oracle	JSON_ARRAYAGG()
DB2	JSON_ARRAYAGG()

Kotlin

// Aggregate child rows as JSON in a single query
fun getOrderLines(customerId: Int): List<OrderLine> =
    sql { """
        SELECT json_group_array(json_array(product, qty, price))
        FROM order_lines
        WHERE customer_id = ${DuckDbTypes.integer(customerId)}
    """ }
        .query(RowCodec.of(linesType).exactlyOne())
        .transact(tx)

Java

// Aggregate child rows as JSON in a single query
List<OrderLine> getOrderLines(int customerId) {
  return Fragment.of(
          """
          SELECT json_group_array(\
          json_array(product, qty, price))
          FROM order_lines
          WHERE customer_id =\
          """)
      .value(DuckDbTypes.integer, customerId)
      .query(RowCodec.of(linesType).exactlyOne())
      .transactRead(tx);
}

Scala

// Aggregate child rows as JSON in a single query
def getOrderLines(customerId: Int): List[OrderLine] =
  sql"""SELECT json_group_array(
            json_array(product, qty, price))
        FROM order_lines
        WHERE customer_id =
            ${DuckDbTypes.integer(customerId)}"""
    .query(RowCodec.of(linesType).exactlyOne())
    .transact(tx)

Show entire file

The type reads the same types your RowCodec defines. There is no separate deserialization layer and no drift between your SQL types and your JSON types.