JSON_OBJECT
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The JSON_OBJECT function is used to produce simple JSON objects from scalar values, without aggregation (see also JSON_OBJECTAGG)
SELECT json_object( KEY 'firstName' VALUE author.first_name, KEY 'lastName' VALUE author.last_name ) FROM author
create.select(jsonObject(
key("firstName").value(AUTHOR.FIRST_NAME),
key("lastName").value(AUTHOR.LAST_NAME)))
.from(AUTHOR)
.fetch();
The result would look like this:
+--------------------------------------------+
| json_array |
+--------------------------------------------+
| {"firstName":"Paulo","lastName":"Coelho"} |
| {"firstName":"George","lastName":"Orwell"} |
+--------------------------------------------+
Dialect support
This example using jOOQ:
jsonObject("firstName", AUTHOR.FIRST_NAME)
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, Postgres, YugabyteDB
json_build_object('firstName', AUTHOR.FIRST_NAME)
ClickHouse
toJSONString(map('firstName', AUTHOR.FIRST_NAME))
DB2, H2, Oracle
json_object(KEY 'firstName' VALUE AUTHOR.FIRST_NAME)
MariaDB, MySQL, SQLite
json_object('firstName', AUTHOR.FIRST_NAME)
Snowflake
object_construct_keep_null('firstName', AUTHOR.FIRST_NAME)
SQLServer
(
SELECT (
SELECT *
FROM (
VALUES (AUTHOR.FIRST_NAME)
) t (firstName)
FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
)
)
Trino
CAST(map_from_entries(ARRAY[row( 'firstName', CAST(AUTHOR.FIRST_NAME AS json) )]) AS json)
ASE, Access, Aurora MySQL, BigQuery, Derby, DuckDB, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, Sybase, Teradata, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!