Json and Oracle 19c

What is JSON

JSON (JavaScript Object Notation) is a lightweight text format for storing and exchanging structured data. The information is organized as key–value pairs and lists.

Assume we have server certificates in json form:

{
  "subject":     "C=CH, ST=Zurich, ...", 
  "certificate": "-----BEGIN CERTIFICATE----...", 
  "valid_until": "2027-07-28" 
}

Or as a one-liner

{  "subject": "C=CH, ST=Zurich, ...", "certificate": "-----BEGIN CERTIFICATE----...", "valid_until": "2027-07-28"}

As we can see, that’s simple ASCII text. And the certificate is usually around 2KB. So that it easily fits in a VARCHAR2(4000) field. For the database it is still a piece of text. That means, we can use it in all database releases. The content has to be parsed in the application.

create table servers (
  servername varchar2(128),
  certificate varchar2(4000),
  constraint SERVERS_PK primary KEY (SERVERNAME)
);

insert into servers (servername, certificate)  values (
 'server01',
 '{ "subject": "C=CH, ST=Zurich, ...", "certificate": "-----BEGIN CERTIFICATE----...", "valid_until": "2027-07-28" }'
);

Json-functionality of Oracle 19c

But wouldn’t it be nice if we can validate the input and select a field in the json like an Oracle field? Something like select certificate.valid_until from servers; ?

In Oracle 18c (or already before?), Oracle introduced some json functions. And there is a trigger validating that it is valid json.

alter table SERVERS add constraint SERVERS_CERT_JSON_CHECK check(CERTIFICATE is json) enable;

And with the json function json_value we can extract the different values of the json object.

select
  SERVERNAME
, json_value(CERTIFICATE, '$.subject') subject
, json_value(CERTIFICATE, '$.valid_until' returning date) valid_until
, trunc(json_value(CERTIFICATE, '$.valid_until' returning date)-sysdate) valid_days
from servers;

Nice, it works. We can use json fields like “normal” database fields.
Somewhere I have seen an example that it should work easier in the form TABLE_FIELD.JSON_FIELD, but…

SQL> select certificate.valid_until from servers;
select certificate.valid_until from servers
       *
ERROR at line 1:
ORA-00904: "CERTIFICATE"."VALID_UNTIL": invalid identifier

Days later, I have seen such a working example again. But what was the difference? There was a query involving several tables, but in my case, there was only one. I tried to join SERVERS with another table. That still did not work. Then I noticed that table aliases were being used. I also added one, but with that, it still didn’t work. The only difference that remained was that they used the table alias as a prefix for the field name. With this change, also my query worked as hoped.

SQL> select s.certificate.valid_until from servers s;

VALID_UNTIL
--------------------------------------------------------------------------------
2027-07-28

To me, that seems illogical and inconsistent. There’s no naming conflict here. And select certificate from servers; also works without alias. For me, it’s a bug, but we have a suitable workaround.

Our query above, we can now simplify it:

select 
  s.SERVERNAME
, s.CERTIFICATE.subject
, s.CERTIFICATE.valid_until
, trunc(to_date(s.CERTIFICATE.valid_until,'yyyy-mm-dd')-sysdate) valid_days
from servers s;