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)
-- 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)
-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, DUCKDB, EXASOL, FIREBIRD, HANA, HSQLDB, INFORMIX, MEMSQL, REDSHIFT,
-- SQLDATAWAREHOUSE, SYBASE, TERADATA, VERTICA
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!