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!