{"id":73096,"date":"2016-10-03T11:03:29","date_gmt":"2016-10-03T11:03:29","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/json-for-absolute-beginners-part-4-retrieving-json-in-oracle\/"},"modified":"2021-07-14T13:07:01","modified_gmt":"2021-07-14T13:07:01","slug":"json-for-absolute-beginners-part-4-retrieving-json-in-oracle","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/json-for-absolute-beginners-part-4-retrieving-json-in-oracle\/","title":{"rendered":"JSON For Absolute Beginners: Part 4 &#8211; Retrieving JSON in Oracle"},"content":{"rendered":"<p>There\u2019s a possibility you\u2019ve parachuted into this series on JSON and Oracle at this point. If you are already familiar with what JSON is and how it is created, and are only interested in discovering how to wrestle JSON data from an Oracle database, then you\u2019re welcome, grab a seat. However, if you see the words JSON and Oracle, and scratch your head wondering if it\u2019s a sequel to the movie, <em>Jason and the Argonauts<\/em>, then you probably should start from <a href=\"https:\/\/allthingsoracle.com\/json-for-absolute-beginners-part-1-introduction\/\">Part 1<\/a>. Don\u2019t worry; you\u2019ll catch up soon enough.<\/p>\n<p>In this article we\u2019ll still be using the very basic json_test table we created in the <a href=\"https:\/\/allthingsoracle.com\/json-for-absolute-beginners-part-3-storing-json-in-oracle\/\">previous article<\/a>. However, to fully stretch the muscles of the functionality Oracle have put together for querying JSON data, we\u2019ll need some JSON data with a few levels of complexity.<\/p>\n<pre>CREATE TABLE json_test\r\n(ID            RAW(16) NOT NULL,\r\n json_data     CLOB    NOT NULL,\r\n created_date  DATE    DEFAULT SYSDATE\r\n);\r\n<\/pre>\n<pre>{\"Title\"\t: \"Lemonade\",\r\n \"AlbumArtist\" \t: \"Beyonce\",\r\n \"ReleaseYear\"\t: 2016,\r\n \"Label\"\t: \"Columbia Records\",\r\n \"Genre\"\t: \"R&amp;B\",\r\n \"Songs\"\t: [{ \"Title\"\t: \"Pray you catch me\",\r\n\t\t     \"Artist\"\t: \"Beyonce\",\r\n\t\t     \"Producer\" : [{\"Name\"\t: \"Beyonce\"},\r\n\t\t\t\t   {\"Name\"\t: \"Jeremy McDonald\"}]},\r\n \t\t    { \"Title\"\t: \"Hold Up\",\r\n\t\t      \"Artist\"\t: \"Beyonce\",\r\n\t\t      \"Producer\": [{\"Name\"\t: \"Diplo\"},\r\n\t\t\t\t   {\"Name\"\t: \"Beyonce\"}]},\r\n\t\t   { \"Title\"\t: \"Don't hurt yourself\",\r\n\t\t     \"Artist\"\t: \"Beyonce\",\r\n                     \"Featuring\": \"Jack White\",\r\n\t\t      \"Producer\": [{\"Name\"\t: \"Derek Dixie\"},\r\n\t\t\t\t   {\"Name\"\t: \"Beyonce\"}]},\r\n\t\t   { \"Title\"\t: \"Sorry\",\r\n\t\t     \"Artist\"\t: \"Beyonce\",\r\n\t\t      \"Producer\": [{\"Name\"\t: \"Melo-X\"},\r\n\t\t\t\t   {\"Name\"\t: \"Beyonce\"}]},\r\n\t\t   { \"Title\"\t: \"Freedom\",\r\n\t\t     \"Artist\"\t: \"Beyonce\",\r\n                     \"Featuring\": \"Kendrick Lamar\",\r\n\t\t      \"Producer\": [{\"Name\"\t: \"Just Blaze\"},\r\n\t\t\t\t   {\"Name\"\t: \"Beyonce\"}]}],\r\n\"ExecutiveProducer\" : \"Beyonce\"}\r\n<\/pre>\n<p>Once we\u2019ve inserted our data into our table we\u2019re ready to rock and roll.<\/p>\n<h5>Dot-Notation<\/h5>\n<p>We\u2019ll come on to talk about all the exciting, high-powered functionality that we have for querying our data, but there are times when you need something really simple and reasonably straightforward. The dot-notation syntax allows you retrieve JSON values easily using a syntax that resembles that you\u2019d use in an ordinary sql statement. The simple dot-notation syntax is as follows: the table alias followed by a dot followed by a JSON column followed by one or more JSON object keys (if there is more than one object key, they must all be separated by dots).<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_object_access_expr.gif\" \/><\/p>\n<p class=\"caption\">Source: Oracle Documentation<\/p>\n<p>There are a few rules that you need to bear in mind:<\/p>\n<ul>\n<li>Your table must have an alias; you cannot choose to skip this step and simply use the table name. It must be aliased.<\/li>\n<li>Your column must be a JSON column, by which I mean that it must be signposted as containing JSON data by the existence of an IS JSON check constraint. As always, while this constraint must exist, it does not need to be enabled.<\/li>\n<li>The JSON object key in your query must be a case-sensitive match for the object key whose value you are seeking to retrieve.<\/li>\n<\/ul>\n<p>Obey these rules and what you get is the following: if your query matches a single value it\u2019ll return that value, but if it matches multiple values it\u2019ll return them as the elements of a JSON array.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_dot_notation.jpg\" \/> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_dot_notation2.jpg\" \/><\/p>\n<p>Simple dot-notation JSON queries return all their results as strings, and can only return up to 4000 characters. Breach that number and, irrespective of your data, it will return a null. To lift that kind of weight you\u2019ll need the muscles of Oracle\u2019s JSON functions, and we\u2019ll talk about them soon.<\/p>\n<p>But first, let me lead you down the garden path.<\/p>\n<h5>Oracle JSON Path Expressions<\/h5>\n<p>One of JSON\u2019s main bragging points is how simple it is; however, in truth, large JSON files can be cobwebs of complex relationships. Oracle\u2019s JSON path expressions are a way of reaching into JSON data and matching particular sections or values. They strongly resemble XPath expressions, mostly for the simple reason that they do for JSON what XPath does for XML: provide a simple means for directly identifying values in the data. For instance:<\/p>\n<pre>$.AlbumArtist\r\n<\/pre>\n<p>is a quick way to identify the album artist &#8211; Beyonce &#8211; from our JSON data. The rules are as follows:<\/p>\n<ul>\n<li>Each path must begin with a dollar sign ($). This is called the <strong>context item<\/strong>, and represents the jumping off point for our path. This point will be identified by the SQL expression that is passed to the Oracle function using the JSON path expression. <br \/>\n The context item is followed by zero or more object steps or array steps.<\/li>\n<li>An object step is a dot (.) followed by an object key (or an asterisk &#8211; * &#8211; to match all keys). The object step returns the value of the matching object key. An asterisk will return the values of all matching object keys in no particular order.<\/li>\n<li>An array step is a left bracket ([), followed by one or more zero-based array indexes (or a wildcard &#8211; *), and bookmarked by a right bracket (]). <br \/>\n There are a few simple, common sense rules regarding array step indexes. To avoid compile-time errors, the indexes must be in ascending order &#8211; [0,2,3] is valid; [0,3,2] is not &#8211; and must not contain repetitions &#8211; [0,2,3] is valid; [0,2,2] is not.<\/li>\n<\/ul>\n<p>Returning to our JSON data, we can come up with some examples that illustrate what we\u2019ve just learned about Oracle JSON path expressions.<\/p>\n<ul>\n<li><em>$.Title<\/em> is the value of the Title object key of the context item. In other words, <em>Lemonade<\/em>.<\/li>\n<li><em>$.Songs[1]<\/em> is the object in the <em>second<\/em> position in the Songs array. The reason why it is the second object is that array indexes are zero-based.<\/li>\n<li><em>$.Songs[0].Title<\/em> is the value of the key Title of the first element in the Songs array. In other words, <em>Pray you catch me<\/em>.<\/li>\n<li><em>$.Songs[*].Producer[0].Name<\/em> is the value of the the first Name key in the Producer array of all elements in the Songs array.<\/li>\n<\/ul>\n<p>It\u2019s all pretty straightforward, but it can make your head hurt if you\u2019re trying to work it out as some kind of mental exercise. What we need is a few functions that actually use JSON path expressions so we can see them in action.<\/p>\n<h5>JSON_VALUE<\/h5>\n<p>JSON_VALUE returns the scalar value of an object key in JSON data. It accepts two mandatory arguments, the first one &#8211; usually a table or view column &#8211; specifying the context item, and the second containing the path expression.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_value.gif\" \/><\/p>\n<p class=\"caption\">Source: Oracle Documentation<\/p>\n<p>Here\u2019s an example:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_value.jpg\" \/><\/p>\n<p>It\u2019s straightforward enough to be self-explanatory. JSON_VALUE, using our path expression returns the value of a JSON key as a SQL value. It\u2019s worth bearing in mind that JSON_VALUE expects your path expression to identify a <em>single <\/em>scalar value; its default behaviour if you violate this or any of its rules is to return a null.<\/p>\n<p>The <strong>error clause<\/strong> is one of JSON_VALUE\u2019s optional arguments. The default behaviour is NULL ON ERROR. However, you can instruct it to ERROR ON ERROR, in which case it will say <em>\u201cORA-40470: JSON_VALUE evaluated to multiple values\u201d<\/em> if your path resolves to more than one value. <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_value_error.jpg\" \/><\/p>\n<p>By default all values returned by calls to JSON_VALUE are of type VARCHAR2 (with a length of 4000). However, you can call an optional <strong>returning clause<\/strong> to specify a data type from a choice of VARCHAR2 or NUMBER. You can also specify the length for VARCHAR2 and precision for NUMBER.<\/p>\n<p>Finally, if your column is a blob you must include the keywords FORMAT JSON to alert Oracle that it should expect JSON data.<\/p>\n<h5>JSON_QUERY<\/h5>\n<p>On the face of it, JSON_QUERY is very similar to JSON_VALUE; the syntax is close to indistinguishable, and its purpose is superficially identical. There are some differences, though. JSON_QUERY returns one <em>or more<\/em> values from JSON data; in contrast to JSON_VALUE which can only return a single value. (Additionally, JSON_QUERY, unlike JSON_VALUE, cannot return the NUMBER data type.) One advantage that this difference gives JSON_QUERY, over its close cousin, is that you can use JSON_QUERY to retrieve fragments of a JSON document.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_query.gif\" \/><\/p>\n<p class=\"caption\">Source: Oracle Documentation<\/p>\n<p>The first argument is the column name (followed by FORMAT JSON if it is of the blob data type). This column is the context item for the next argument.<\/p>\n<p>The next required argument is a path expression.<\/p>\n<p>The next argument is optional and is the returning clause. Unlike JSON_VALUE, JSON_QUERY cannot return numbers; you can only specify VARCHAR2. However, you can use this clause to declare a size for your string.<\/p>\n<p>The next argument is one we haven\u2019t discussed yet. It\u2019s the <strong>wrapper clause<\/strong>. This optional parameter allows you specify the form in which JSON_QUERY will return its output. What this means will become clearer once I tell you what the options are:<\/p>\n<p>WITH WRAPPER: returns a string that represents a JSON array of all the values that match your path expression. What this means is that if your path expression, for example, resolves to <em>Beyonce<\/em>, your function will return <em>[Beyonce]<\/em>. If, however, it resolves to <em>{Name: Beyonce}<\/em>, it\u2019ll return <em>[{Name: Beyonce}]<\/em>. And if your path resolves to a null, it\u2019ll return [ ].<\/p>\n<p>WITHOUT WRAPPER: returns a string that represents the single JSON object or array that matches your path expression. Single. Object or Array. What this means is that if your path resolves to multiple objects or array, or if it resolves to one or more values, it\u2019ll error. Using our earlier examples, <em>Beyonce <\/em>will error because it is a value. However, the object <em>{Name: Beyonce}<\/em> will be returned as, well, <em>{Name: Beyonce}<\/em>. A null will error also, as it is not a single object or array.<\/p>\n<p>The default behaviour for JSON_QUERY is WITHOUT WRAPPER.<\/p>\n<p>Following the wrapper clause as an optional argument for JSON_QUERY is the error clause. There\u2019s a new option for the error clause that only exists for JSON_QUERY; it is EMPTY ON ERROR. This means the function should return an empty array, [ ], on error.<\/p>\n<p>To illustrate the contrast between JSON_QUERY and JSON_VALUE, here\u2019s that path expression that resulted in an error for JSON_VALUE.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_query.jpg\" \/><\/p>\n<p>Notice how we were able to return multiple values, and how, using the WITH WRAPPER clause, they are wrapped in an array. If we used the WITHOUT WRAPPER option for the same expression, we run up against an error.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_query_ERROR.jpg\" \/><\/p>\n<h5>JSON_TABLE<\/h5>\n<p>If you\u2019re conversant with the Oracle XQuery function <a href=\"https:\/\/allthingsoracle.com\/xquery-for-absolute-beginners-part-2-xquery-and-the-oracle-database\/\" target=\"_blank\">XMLTable<\/a>, then the thinking behind JSON_TABLE will be familiar to you. Basically, it\u2019s a means of transforming JSON data into a relational format &#8211; in other words, rows and columns. It\u2019s a really powerful tool, if you think about it. It enables you take JSON data and decompose it to a virtual table upon which you can carry out your everyday SQL operations.<\/p>\n<p>Its syntax is as follows:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_table.gif\" \/><\/p>\n<p class=\"caption\">Source: Oracle Documentation<\/p>\n<p>The first mandatory argument, as always, is the source JSON column. This is followed by the path expression.<\/p>\n<p>Next is the optional error clause. The options here are, unsurprisingly, ERROR ON ERROR and NULL ON ERROR, with the latter being the default.<\/p>\n<p>Finally, there is the mandatory <strong>columns clause<\/strong>. This clause provides the definition for the columns of the virtual table. The column clause deserves a closer look. It begins with the keyword COLUMNS and is followed by the column definitions, ensconced in braces.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_columns_clause.gif\" \/><\/p>\n<p class=\"caption\">Source: Oracle Documentation<\/p>\n<p>The column definitions are made up of a column name, a return type, the keyword PATH and the path expression that resolves to the value you want to populate your column with.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_value_column.gif\" \/><\/p>\n<p class=\"caption\">Source: Oracle Documentation<\/p>\n<p>An example would probably help at this juncture. Let\u2019s get a track listing from our album.<\/p>\n<pre>SELECT jt.*\r\nFROM json_test,\r\nJSON_TABLE (json_data, '$.Songs[*]'\r\nCOLUMNS (row_number FOR ORDINALITY,\r\n         song            VARCHAR2(50) PATH '$.Title',\r\n         artist          VARCHAR2(30) PATH '$.Artist',\r\n         featured_artist VARCHAR2(30) PATH '$.Featuring'\r\n ))\r\nAS jt;\r\n<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/10\/json_table_results.jpg\" \/><\/p>\n<p>There are a few things I should point out. First, notice how the table name &#8211; in this case, <em>json_test<\/em> &#8211; must be part of the FROM clause, right before the call to JSON_TABLE.<\/p>\n<p>Second, notice how the main path expression provides the context item for the column path expression.<\/p>\n<p>Finally, a column with the FOR ORDINALITY keyword returns generated row numbers.<\/p>\n<h5>Conclusion<\/h5>\n<p>And that&#8217;s it for this lesson on querying JSON data in an Oracle database. In the final article in this series we&#8217;ll be talking about generating JSON data from relational data. Hint: it&#8217;s not as easy as it should be.<\/p>\n<p><span style=\"text-decoration: underline;\"><em>Reference:<\/em><\/span><\/p>\n<p><a href=\"https:\/\/docs.oracle.com\/database\/121\/ADXDB\/json.htm#ADXDB6246\" target=\"_blank\">Oracle documentation: JSON in Oracle<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>There\u2019s a possibility you\u2019ve parachuted into this series on JSON and Oracle at this point. If you are already familiar with what JSON is and how it is created, and are only interested in discovering how to wrestle JSON data from an Oracle database, then you\u2019re welcome, grab a seat. However, if you see the words JSON and Oracle, and&hellip;<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[4880,48433,48434,48435,4459],"coauthors":[],"class_list":["post-73096","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-json","tag-json_query","tag-json_table","tag-json_value","tag-oracle"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73096","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73096"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73096\/revisions"}],"predecessor-version":[{"id":91596,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73096\/revisions\/91596"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73096"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73096"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73096"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73096"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}