A recent forum post prompted me to look into JSON syntax rules and their implications more closely. The way I read the JSON Developer’s Guide there are two aspects to the topic of lax and strict JSON syntax:
- When storing JSON as a database column
- Performing type conversions as part of JSON functions like JSON_QUERY, etc.
Let’s look at the first of these – storing JSON – before diving into type conversions in a future article.
Here is the list of posts of this series:
- Part 1: storing JSON in Oracle Database 19c and 23ai (this document)
- Part 2: Dealing with type Conversions when using the simple dot access notation
- Part 3: Handling type conversions using JSON-SQL functions
Storing JSON in a table
Oracle Database has supported JSON since database release 12c, released more than a decade ago. Since then, a lot has happened, culminating (at least in my opinion) with the introduction of a new, dedicated data type for JSON in Oracle Database 21c. If you are interested in the OSON JSON data type, you find all the details you can ever imagine in this Medium article including a comparison with other JSON data type implementations in databases.
JSON columns in Releases prior to Database 21c
JSON was stored as text, that is, either as a VARCHAR2, BLOB, or CLOB column up to Oracle Database 21c. This implied that you could store arbitrary content, and even syntactically incorrect JSON, in a table, if you weren’t careful.
The following example is for demonstration purposes only, please don’t do this, not even at home.
create table t1 (
id number generated by default on null as identity
constraint pk_t1 primary key,
json_column clob
);
Without a safety net it is possible to insert invalid JSON:
SQL> insert into t1 ( json_column ) values ( 'this is not valid json' );
1 row inserted.
SQL> rollback;
Rollback complete
To ensure you only ever store valid JSON, you add a check constraint to the JSON-text column, as shown in this example:
alter table t1 add constraint c_t1_json check (json_column is json);
This is probably much more aligned with what you intended, and you most certainly don’t want to store JSON without this check constraint. And if you are on a new Oracle Database release (21c or later) you shouldn’t store JSON as text anymore, use the JSON data type instead. More on that topic later.
Invalid JSON must be caught during write operations, and that’s exactly what the check constraint does.
SQL> insert into t1 ( json_column ) values ( 'this is not valid json' );
Error starting at line : 1 in command -
insert into t1 ( json_column ) values ( 'this is not valid json' )
Error report -
ORA-02290: check constraint (MARTIN.C_T1_JSON) violated
From now on, your JSON has to be valid, or it is rejected:
SQL> insert into t1 (json_column) values ('{ valid: true }');
1 row inserted.
SQL> insert into t1 (json_column) values ('{ valid: true, someString: "some string", someNumber: 1234 }');
1 row inserted.
But wait, there is more to it!
Strict vs. Lax Syntax
Note that the above insert statement is written using the so-called lax syntax. The example violates the rule mandated by the standard – each JSON field and each string value must be enclosed in double quotation marks.
Clearly this isn’t the case in the above example:
SQL> select json_column from t1;
JSON_COLUMN
_______________________________________________________________
{ valid: true }
{ valid: true, someString: "some string", someNumber: 1234 }
With textual JSON the database doesn’t modify the document on write.
The above is still valid JSON, but every development tool adds squiggly lines to it. Being able to store these JSON documents is not a bug. In fact it is intended behaviour, as per the documentation:
On input, the Oracle default syntax for JSON is lax. It reflects the JavaScript syntax for object fields; the Boolean and null values are not case-sensitive; and it is more permissive with respect to numerals, whitespace, and escaping of Unicode characters. Oracle functions return JSON data that strictly respects the standard.
What does that mean? Sticking with the above example, you can see that it is possible to insert JSON into a CLOB column that doesn’t adhere to the strict syntax rules (= lax on input)
However, if you access the table using a function like json_serialize, you will notice that Oracle functions return JSON adhering to the strict syntax rules:
SQL> select
2 id,
3 json_serialize(j.json_column pretty) as strict_json
4 from
5* t1 j;
ID STRICT_JSON
_____ ______________________________________________________________________________
1 {
"valid" : true
}
2 {
"valid" : true,
"someString" : "some string",
"someNumber" : 1234
}
That’s strict syntax on output for you. Note that double-quoted fields!
Enforcing strict syntax rules for textual JSON
At this point it’s important to point out that all examples are based on storing JSON as a CLOB, in other words, it’s text representation. At the risk of repeating myself, beginning with Oracle Database 21c you should really use the new JSON data type instead of a BLOB, CLOB, or VARCHAR2.
You can still enforce strict JSON on write, even if you haven’t made the move to Oracle Database 23ai yet.
The is json check constraint shown earlier declared the developer’s intent to only accept valid JSON. You can take this a step further by mandating strict JSON, too.
create table t2 (
id number generated by default on null as identity
constraint pk_t2 primary key,
json_column clob,
constraint c_t2_json check (json_column is json (strict))
);
By adding is json (strict) you effectively disabled:
- Invalid JSON
- JSON not adhering to the strict syntax rules
This is evident in the following insert:
SQL> insert into t2 (json_column) values (
2 '{
3 valid: true,
4 someString: "some string",
5 someNumber: 1234
6 }'
7* );
Error starting at line : 1 in command -
insert into t2 (json_column) values (
'{
valid: true,
someString: "some string",
someNumber: 1234
}'
)
Error report -
ORA-02290: check constraint (MARTIN.C_T2_JSON) violated
Only after rewriting the input to follow strict syntax rules does the insert succeed:
SQL> insert into t2 (json_column) values (
2 '{
3 "valid" : true,
4 "someString" : "some string",
5 "someNumber" : 1234
6 }'
7* );
1 row inserted.
SQL> select json_column from t2 where id = 5;
JSON_COLUMN
______________________________________________________________________________
{
"valid" : true,
"someString" : "some string",
"someNumber" : 1234
}
Well, that’s a thing or two to respect working with JSON. Thankfully, things have improved a lot!
What about newer Releases?
The good news is: you don’t need to concern yourself as much about the way you store JSON in the database in Oracle Database 21c and newer. Thanks to the new JSON data type and the JSON constructor you should be in a great place.
The table DDL is much simplified:
create table t3 (
id number generated by default on null as identity
constraint pk_t3 primary key,
json_column JSON
);
There is no need for JSON-specific check constraints (you may require others though!). You can still insert data using lax syntax …
insert into t3 (json_column) values (
'{
valid: true,
someString: "some string",
someNumber: 1234
}'
);
… but you will notice that when querying it you get _strict syntax_ JSON back. Even if you don’t explicitly specify a JSON% function:
SQL> select json_column from t3;
JSON_COLUMN
______________________________________________________________
{"valid":true,"someString":"some string","someNumber":1234}
No change to the way Oracle returns JSON if using functions:
SQL> select json_serialize(json_column pretty) as pretty_json from t3;
PRETTY_JSON
______________________________________________________________________________
{
"valid" : true,
"someString" : "some string",
"someNumber" : 1234
}
You cannot store invalid JSON, even though there is no is json constraint.
SQL> insert into t3 (json_column) values ('this will not work');
Error starting at line : 1 in command -
insert into t3 (json_column) values ('this will not work')
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-40441: JSON syntax error
JZN-00078: Invalid JSON keyword 'this' (line 1, position 1)
If you like you can insert the data using the JSON constructor:
SQL> insert into t3 (json_column) values ( json('{ valid: true, someNumber: 3, anotherNumber: "4" }'));
1 row inserted.
SQL> select * from t3 where id = 5;
ID JSON_COLUMN
_____ ____________________________________________________
5 {"valid":true,"someNumber":3,"anotherNumber":"4"}
If you look closely, you’ll notice that anotherNumber was inserted as a string – this should serve as a teaser for the next article.
Summary
Oracle Database 21c and newer provide a native JSON data type. This is the one you should be using as it offers a lot of advantages out of the box. Apart from the way you work with the JSON data type, there are lots of invisible benefits under the bonnet, please have a look at the JSON Developer’s Guide for all the details.
If you aren’t yet on Oracle Database 23ai you will have to use a textual representation of JSON data. In that case you are almost guaranteed in need of an is json check constraint. Depending on your application you might want to consider a requirement for strict JSON as well. The day your migration project to Oracle Database 23ai starts, please consider switching from a textual JSON representation to the native JSON data type.
Martins Blog 