{"id":20583,"date":"2021-09-29T08:50:35","date_gmt":"2021-09-28T22:50:35","guid":{"rendered":"https:\/\/database.guide\/?p=20583"},"modified":"2021-09-29T08:50:35","modified_gmt":"2021-09-28T22:50:35","slug":"json_object-function-in-oracle","status":"publish","type":"post","link":"https:\/\/database.guide\/json_object-function-in-oracle\/","title":{"rendered":"JSON_OBJECT() Function in Oracle"},"content":{"rendered":"\n<p>In Oracle Database, the <code>JSON_OBJECT()<\/code> function creates a JSON object from a sequence of key-value pairs or one object type instance.<\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<p>The syntax goes like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>JSON_OBJECT\n  ( &#91; &#91; KEY ] key_expr VALUE val_expr &#91; FORMAT JSON ]\n     ,]...\n    &#91; JSON_on_null_clause ] &#91; JSON_returning_clause ]\n    &#91; STRICT ] \n    &#91; WITH UNIQUE KEYS ]  )<\/code><\/pre>\n\n\n\n<p>Where:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><code>[ KEY ] key_expr VALUE val_expr<\/code> specifies a property key-value pair.<\/li><li><code>FORMAT JSON<\/code> indicates that the input string is JSON, and will therefore not be quoted in the output.<\/li><li><em><code>JSON_on_null_clause<\/code><\/em> specifies the behaviour to use when <em><code>expr<\/code><\/em> equates to null (i.e. either include null values in the output or don&#8217;t).<\/li><li><em><code>JSON_returning_clause<\/code><\/em> specifies&nbsp;the type of return value.<\/li><li><code>STRICT<\/code> checks whether or not the output of the JSON generation function is correct JSON. If the check fails, a syntax error is raised.<\/li><li><code>WITH UNIQUE KEYS<\/code> guarantees that generated JSON objects have unique keys.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p>Here&#8217;s an example to demonstrate how it works:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_OBJECT(KEY 'score' VALUE 37) FROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{\"score\":37}<\/pre>\n\n\n\n<p>The <code>KEY<\/code> part is optional, so the following achieves the same result:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_OBJECT('score' VALUE 37) FROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{\"score\":37}<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Multiple Key\/Value Pairs<\/h2>\n\n\n\n<p>Here&#8217;s an example with multiple key\/value pairs:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_OBJECT(\n    KEY 'fname' VALUE 'Homer',\n    KEY 'lname' VALUE 'Escobar',\n    KEY 'score' VALUE 237\n    ) \nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{\"fname\":\"Homer\",\"lname\":\"Escobar\",\"score\":237}<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Nested JSON<\/h2>\n\n\n\n<p>If one of the values you pass contains JSON, you can use the <code>FORMAT JSON<\/code> argument to specify that it contains JSON and should not be quoted in the output.<\/p>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_OBJECT(\n    KEY 'details' \n    VALUE '{\"name\": \"Peter\", \"score\": 64}' \n    FORMAT JSON\n    ) \nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{\"details\":{\"name\": \"Peter\", \"score\": 64}}<\/pre>\n\n\n\n<p>Here&#8217;s what happens if we remove the <code>FORMAT JSON<\/code> part:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_OBJECT(\n    KEY 'details' \n    VALUE '{\"name\": \"Peter\", \"score\": 64}'\n    ) \nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{\"details\":\"{\\\"name\\\": \\\"Peter\\\", \\\"score\\\": 64}\"} <\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Database Example<\/h2>\n\n\n\n<p>Suppose we run the following query:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT *\nFROM regions;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">   REGION_ID               REGION_NAME \n____________ _________________________ \n           1 Europe                    \n           2 Americas                  \n           3 Asia                      \n           4 Middle East and Africa    <\/pre>\n\n\n\n<p>Here&#8217;s the same query, but with the selected columns passed to the <code>JSON_OBJECT()<\/code> function:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_OBJECT(*) \nFROM regions;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{\"REGION_ID\":1,\"REGION_NAME\":\"Europe\"}                    \n{\"REGION_ID\":2,\"REGION_NAME\":\"Americas\"}                  \n{\"REGION_ID\":3,\"REGION_NAME\":\"Asia\"}                      \n{\"REGION_ID\":4,\"REGION_NAME\":\"Middle East and Africa\"}<\/pre>\n\n\n\n<p>And here it is with just one column passed to the function:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_OBJECT(region_name) \nFROM regions;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{\"region_name\":\"Europe\"}                    \n{\"region_name\":\"Americas\"}                  \n{\"region_name\":\"Asia\"}                      \n{\"region_name\":\"Middle East and Africa\"} <\/pre>\n\n\n\n<p>See <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/21\/sqlrf\/JSON_OBJECT.html\" data-type=\"URL\" data-id=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/21\/sqlrf\/JSON_OBJECT.html\" target=\"_blank\" rel=\"noreferrer noopener\">Oracle&#8217;s documentation<\/a> for a more detailed explanation of this function.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle Database, the JSON_OBJECT() function creates a JSON object from a sequence of key-value pairs or one object type instance.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[93,80,20],"class_list":["post-20583","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-functions","tag-json","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/20583","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=20583"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/20583\/revisions"}],"predecessor-version":[{"id":20642,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/20583\/revisions\/20642"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=20583"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=20583"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=20583"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}