JSON supports both a lax and a strict syntax. But what does this mean? In this 2 part article series you can find out! This part deals with writing and storing JSON. It also applies to a textual representation of JSON. Oracle Database 21c and later support a native JSON data type, the entire article you are about to read applies to releases prior to 21c – 19c and older. If you are using Oracle Database 23ai please use the native JSON data type instead. You may also strongly consider migrating from a textual JSON representation to the native data type in existing schemas.
JSON lax and strict syntax
As per the JSON Developer’s Guide there are two aspects to the topic of lax and strict JSON:
- 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. Again, this mostly applies to Oracle Database 19c, newer releases feature a dedicated JSON data type making your lives a lot easier.
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 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 Release 21c
In releases leading up to Oracle Database 21c, JSON was stored as text, that is, either as a VARCHAR2, BLOB, or CLOB column. 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 in Oracle Database 19c and earlier. If you use the native JSON data type in Oracle Database 21c or later, you don’t need to worry about incorrect JSON: it will be rejected.
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
Invalid JSON is rejected, only valid JSON passes:
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 for textual JSON representation
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 }
When storing “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. 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
}
Note that double-quoted fields valid, someString, and someNumber.
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.
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))
);
Now 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 correcting the input to strict syntax 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
}
What about newer Releases than Oracle Database 19c?
The good news is: you don’t need to concern yourself 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:
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: it addresses type conversions.
Summary
Releases up to Oracle Database 21c required a few extra steps to enforce valid JSON adhering to strict syntax rules. 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. It’s easy, and will pay off.