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

previous : next

JSON (JSON)

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

The JSON data type represents a possibly formatted JSON document, or org.jooq.JSON in Java. It has no direct representation in JDBC.

DDL support

Dialect support

This example using jOOQ:

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

Translates to the following dialect specific expressions:

Access

CREATE TABLE t (
  c longtext
)

ASE, Sybase

CREATE TABLE t (
  c text NULL
)

Aurora MySQL, Aurora Postgres, BigQuery, CockroachDB, DuckDB, Exasol, H2, MariaDB, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, Trino, YugabyteDB

CREATE TABLE t (
  c json
)

ClickHouse

CREATE TABLE t (
  c JSON
)
ENGINE Log()

Databricks

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

DB2, Derby, HSQLDB, Hana, SQLite, Teradata

CREATE TABLE t (
  c clob
)

Firebird

CREATE TABLE t (
  c blob sub_type text
)

Informix, MemSQL

CREATE TABLE t (
  c text
)

Snowflake

CREATE TABLE t (
  c variant
)

Vertica

CREATE TABLE t (
  c long varchar
)

Cast support

Dialect support

This example using jOOQ:

cast(field("c"), JSON)

Translates to the following dialect specific expressions:

Access

cstr(c)

ASE, Informix, Sybase

CAST(c AS text)

Aurora MySQL, Aurora Postgres, BigQuery, CockroachDB, Databricks, DuckDB, Exasol, H2, MariaDB, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, Trino, YugabyteDB

CAST(c AS json)

ClickHouse

CAST(c AS JSON)

DB2, Derby, HSQLDB, Hana, SQLite, Teradata

CAST(c AS clob)

Firebird

CAST(c AS blob sub_type text)

MemSQL

CAST(c AS char)

Snowflake

CAST(c AS variant)

Vertica

CAST(c AS long varchar)

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

previous : next