The jOOQ User Manual SQL building Data types Built-in data types JSONB (JSONB)

previous : next

JSONB (JSONB)

Supported by ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The JSONB data type represents a pre-processed, binary-stored JSON document, or org.jooq.JSONB in Java. It has no direct representation in JDBC.

DDL support

Dialect support

This example using jOOQ:

createTable("t").column("c", JSONB)

Translates to the following dialect specific expressions:

Access

CREATE TABLE t (
  c binary
)

ASE, Sybase

CREATE TABLE t (
  c binary NULL
)

Aurora MySQL, Aurora Postgres, CockroachDB, Postgres, Redshift, SQLDataWarehouse, SQLServer, YugabyteDB

CREATE TABLE t (
  c jsonb
)

BigQuery, DuckDB, Exasol, H2, MariaDB, MySQL, Oracle, Trino

CREATE TABLE t (
  c json
)

ClickHouse

CREATE TABLE t (
  c JSON
)
ENGINE Log()

Databricks

CREATE TABLE t (
  c jsonb
)
TBLPROPERTIES(
  'delta.columnMapping.mode' = 'name',
  'delta.feature.allowColumnDefaults' = 'supported'
)

DB2, Derby, Firebird, HSQLDB, Hana, Informix, MemSQL, SQLite, Teradata

CREATE TABLE t (
  c blob
)

Snowflake

CREATE TABLE t (
  c variant
)

Vertica

CREATE TABLE t (
  c long varbinary
)

Cast support

Dialect support

This example using jOOQ:

cast(field("c"), JSONB)

Translates to the following dialect specific expressions:

Access

cstr(c)

ASE, MemSQL, Sybase

CAST(c AS binary)

Aurora MySQL, Aurora Postgres, CockroachDB, Databricks, Postgres, Redshift, SQLDataWarehouse, SQLServer, YugabyteDB

CAST(c AS jsonb)

BigQuery, DuckDB, Exasol, H2, MariaDB, MySQL, Oracle, Trino

CAST(c AS json)

ClickHouse

CAST(c AS JSON)

DB2, Derby, Firebird, HSQLDB, Hana, Informix, SQLite, Teradata

CAST(c AS blob)

Snowflake

CAST(c AS variant)

Vertica

CAST(c AS long varbinary)

The jOOQ User Manual SQL building Data types Built-in data types JSONB (JSONB)

previous : next