{"id":73101,"date":"2016-09-16T15:54:41","date_gmt":"2016-09-16T15:54:41","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/json-for-absolute-beginners-part-3-storing-json-in-oracle\/"},"modified":"2021-07-14T13:07:02","modified_gmt":"2021-07-14T13:07:02","slug":"json-for-absolute-beginners-part-3-storing-json-in-oracle","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/json-for-absolute-beginners-part-3-storing-json-in-oracle\/","title":{"rendered":"JSON For Absolute Beginners: Part 3 – Storing JSON in Oracle"},"content":{"rendered":"

Welcome to part three in this series of articles about Oracle and its uneasy romance with JSON. Uneasy? Well, that\u2019s a bit of a spoiler; so far it’s been smooth sailing. Part 1<\/a> was a precis of JSON, while Part 2 <\/a>delved into the reasons for Oracle\u2019s involvement in the area. So far so good; but now we\u2019ve got to talk how<\/em>.<\/p>\n

In other words – and to (over)stretch the romance analogy – we\u2019ve introduced our main characters, they\u2019ve fallen in love, and now we\u2019re approaching the scene where they have a big fight and JSON screams, \u201cYou don\u2019t care about me! You don\u2019t do for me the things you do for \u2026 her<\/em>.\u201d<\/p>\n

\u201cWho?\u201d<\/p>\n

\u201cYour ex, XML!\u201d<\/p>\n

Aargh, stop me before I get carried away; this is a technical document, not a romance novel.<\/p>\n

However, there\u2019s a serious point buried in all that fluff; Oracle\u2019s offering for JSON pales in comparison to their robust suite of XML functionality. But let\u2019s not get ahead of ourselves; there\u2019s still a huge amount to learn. Let\u2019s start at the start: storing JSON in an Oracle database.<\/p>\n

Storing JSON Data<\/h5>\n
\n

\u201cYou don\u2019t do for me the things you do for her \u2026 for XML!\u201d<\/p>\n<\/blockquote>\n

Unlike XML which has its own personal data type – XMLType – there isn\u2019t a specific data type dedicated solely to JSON data. It is recommended that you store your JSON documents in either a VARCHAR2 column, a CLOB or a BLOB.<\/p>\n

JSON documents, you will recall, are stored wholesale and not broken down into their atomic values in the way that good normalization would have us do with relational data. To that end, you may have a table – let\u2019s call ours json_test\u00a0–\u00a0<\/em>with an ID column, and you\u2019d want a second column to store your JSON data in. So which do you choose – VARCHAR2, CLOB or BLOB?<\/p>\n

There are a few differences. The difference between VARCHAR2 and CLOB, as you know, is size. Take the size of your JSON documents into account when choosing between the two. However, you should know that Oracle uses UTF-8 when processing JSON data. This means that if your data is not UTF-8 it will carry out automatic character-set conversion. If this is something that you wish to guard against you may want to store your JSON data in a BLOB, which doesn\u2019t store JSON textually, and therefore is never converted.<\/p>\n

One final point on the topic: if you decide to store your JSON data in a BLOB, you\u2019ll need to inform Oracle that it is JSON data each time you process it by using the FORMAT JSON keyphrase. More on that later.<\/p>\n

But for now, let\u2019s build that table we talked about, json_test<\/em>.<\/p>\n

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
Check Constraints<\/h5>\n
So here\u2019s a question. If we\u2019re putting our JSON data in a bog-standard VARCHAR2 column or CLOB and don\u2019t have the protection of an XMLType kind of data type, what\u2019s to stop us from accidentally storing other types of data in the same column? The clever bods at Oracle have thought of that, and have introduced two new JSON conditions: is json<\/em> and is not json<\/em>. These conditions can be used in case statements and in where clauses, but it is very important that we also use is json<\/em> in check constraints on columns designed to hold JSON data. Let me show you.<\/p>\n
ALTER TABLE json_test\r\nADD CONSTRAINT check_is_json\r\n CHECK (json_data IS JSON);\r\n<\/pre>\n
If you trust your data source and are aware that check constraints sometimes come with a performance cost, you might be tempted to forgo the constraint. Don\u2019t. In addition to acting as bouncers at your data\u2019s door, these check constraints also inform Oracle that this column contains JSON data. To this end there are a number of new data dictionary views: ALL_JSON_COLUMNS<\/a>, DBA_JSON_COLUMNS and USER_JSON_COLUMNS that pick up all columns that have an is json<\/em> check constraint. If you drop your check constraint your column will disappear from these views; however if you only disable<\/em> it the column will still be picked up by the data dictionary.<\/p>\n
(You may have noticed that I\u2019ve not said anything about the is not json<\/em> condition. It obviously doesn\u2019t make any sense to use it in our check constraints; however, as I\u2019ve said, these conditions can both be used in other ways too. Let\u2019s put is not json<\/em> out of our minds for now; we\u2019ll return to it in the next article in this series when we discuss using these conditions in where clauses.)<\/p>\n
Strict vs Lax Syntax<\/h5>\n
Remember back when you were a teenager and you had this friend whose parents were a little more laidback than everyone else\u2019s. They\u2019d let you drink beer in their house, and they wouldn\u2019t tell you off when you road-tested a swear word in their presence. Well, Oracle is a bit like those parents when it comes to policing JSON syntax. By default, Oracle\u2019s syntax for JSON is lax. What this means is that it\u2019s not fussed about certain syntax rules.<\/p>\n