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. Foundations gives you a unified way to work with JSON across all of them: your RowCodec doubles as a JSON codec with zero 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.decimal(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.decimal(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.decimal(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 has these methods:

Database	Methods
PostgreSQL	PgTypes.jsonArrayEncoded / jsonbArrayEncoded + object variants
MariaDB/MySQL	MariaTypes.jsonArrayEncoded + object variants
DuckDB	DuckDbTypes.jsonArrayEncoded + object variants
SQL Server	SqlServerTypes.jsonArrayEncoded + object variants
Oracle	OracleTypes.jsonArrayEncoded + object variants
DB2	Db2Types.jsonArrayEncoded + object variants

Each method also has a List variant (e.g. jsonArrayEncodedList) for columns that hold a JSON array of rows.

Aggregating Child Rows as JSON

The real power of JSON-encoded types shows when you aggregate child rows directly 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())
            .transact(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 — no separate deserialization layer, no mapping code, no drift between your SQL types and your JSON types.