The jOOQ User Manual : SQL building : Column expressions : JSON functions : JSON_OBJECT | previous : next |
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 json_build_object('firstName', AUTHOR.FIRST_NAME) -- DB2, H2, ORACLE JSON_OBJECT(KEY 'firstName' VALUE AUTHOR.FIRST_NAME) -- MARIADB, MYSQL JSON_OBJECT('firstName', AUTHOR.FIRST_NAME) -- SQLSERVER (SELECT * FROM (VALUES (AUTHOR.FIRST_NAME)) t (firstName) FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) -- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, FIREBIRD, HANA, HSQLDB, IGNITE, INFORMIX, INGRES, MEMSQL, REDSHIFT, SNOWFLAKE, -- SQLDATAWAREHOUSE, SQLITE, SYBASE, TERADATA, VERTICA /* UNSUPPORTED */
(These are currently generated with jOOQ 3.15, see #10141)
Feedback
Do you have any feedback about this page? We'd love to hear it!