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

In other words – and to (over)stretch the romance analogy – we’ve introduced our main characters, they’ve fallen in love, and now we’re approaching the scene where they have a big fight and JSON screams, “You don’t care about me! You don’t do for me the things you do for … her.”

“Who?”

“Your ex, XML!”

Aargh, stop me before I get carried away; this is a technical document, not a romance novel.

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

Storing JSON Data

“You don’t do for me the things you do for her … for XML!”

Unlike XML which has its own personal data type – XMLType – there isn’t 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.

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’s call ours json_test – with an ID column, and you’d want a second column to store your JSON data in. So which do you choose – VARCHAR2, CLOB or BLOB?

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’t store JSON textually, and therefore is never converted.

One final point on the topic: if you decide to store your JSON data in a BLOB, you’ll need to inform Oracle that it is JSON data each time you process it by using the FORMAT JSON keyphrase. More on that later.

But for now, let’s build that table we talked about, json_test.

Check Constraints

So here’s a question. If we’re putting our JSON data in a bog-standard VARCHAR2 column or CLOB and don’t have the protection of an XMLType kind of data type, what’s 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 and is not json. These conditions can be used in case statements and in where clauses, but it is very important that we also use is json in check constraints on columns designed to hold JSON data. Let me show you.

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’t. In addition to acting as bouncers at your data’s 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, DBA_JSON_COLUMNS and USER_JSON_COLUMNS that pick up all columns that have an is json check constraint. If you drop your check constraint your column will disappear from these views; however if you only disable it the column will still be picked up by the data dictionary.

(You may have noticed that I’ve not said anything about the is not json condition. It obviously doesn’t make any sense to use it in our check constraints; however, as I’ve said, these conditions can both be used in other ways too. Let’s put is not json out of our minds for now; we’ll return to it in the next article in this series when we discuss using these conditions in where clauses.)

Strict vs Lax Syntax

Remember back when you were a teenager and you had this friend whose parents were a little more laidback than everyone else’s. They’d let you drink beer in their house, and they wouldn’t 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’s syntax for JSON is lax. What this means is that it’s not fussed about certain syntax rules.

• The rule is that JSON fields and strings must be enclosed in double quotation marks? Oracle’ll let you use single quotes.
• The rule is that Booleans – true, false, null – must be in lowercase? Oracle’ll let you use wHaTEver CasE YoU waNT.

There are other features of Oracle’s lax JSON syntax policing, and, if you’re like me you’re probably thankful for it. However, you may, in certain instances, want your JSON to abide by a strict syntax. To do this you can use the STRICT keyword with the is json condition. This will tell Oracle to test if JSON data is well-formed using strict rules.

Please note that STRICT must be enclosed in brackets.

Unique Object Member Names

We’ve established that JSON is not fussy about certain rules. For example, the following is – understandably – valid JSON data:

However, the following is also completely valid JSON data, despite its obvious problem:

Whilst it is obviously unwise to have two fields with the exact same name within the exact same object, it does not violate any rules. If, however, you wish to enforce uniqueness amongst an object’s members’ names you can use the WITH UNIQUE KEYS key phrase with the is json condition. (You can also use WITHOUT UNIQUE KEYS. But since this is the default behaviour there’s no real need to waste your energy typing out the words.)

It is worth bearing in mind that the WITH UNIQUE KEYS clause does come with a performance pricetag as the whole document will need to be loaded into memory before the check for duplicates can be carried out. To be honest with you, it’s not a check that I use myself; however, it’s probably useful that you know about it in case you ever need it.

Conclusion

So finally we are in a position where we can load our JSON data into our Oracle database: we’ve created our table and applied a check constraint to ensure that our JSON is well-formed.

In the next article in this series we’ll take a look at the functionality that exists for querying our data.