Mastering 23c Developer free: SQL Domains and JSON Schema

Oracle just released the free version of the Oracle Database 23c for developers. This version provides early access to great features that simplifies the development of new data-driven applications. In this blog we are going to cover a few of them. If you are interested to have a look into the announcement, you can read it here.

The first feature we are going to explain is the SQL Domains. In a SQL Domain we define properties and constraints. This allows developers to better understand how data is used and improves overall data quality.

Let’s start with an easy example. We want to create a table with customer information. This customer will be oriented for the marketing department and we want to store some info about them and a discount voucher. We are going to define a constraint to limit the amount voucher, how do we want to display and order it and finally some annotations.

Annotations is another great capability which allows developer to store metadata directly with the data. In this case we are going to store some information about the purpose of this voucher:

CREATE DOMAIN  voucher AS  Number
DEFAULT ON NULL 0
CONSTRAINT voucher_limits CHECK (voucher > = 0 and voucher <=500) ENABLE
DISPLAY TO_CHAR(voucher, '$999.99')  
ORDER ( -1*voucher) 
ANNOTATIONS (properties '{"Department": "Marketing", "Country": "USA", "Max Voucher": "500$" }');

SQL Domains provide great flexibility and we can define any kind of constraint. We are going to create a new domain to be sure that emails inserted into the table have the right email format using regular expressions. Moreover, we are going to define a display property to mask the email:

CREATE DOMAIN email_domain as varchar2(100)
constraint email_check check (regexp_like (email_domain, '^(\S+)\@(\S+)\.(\S+)$'))
DISPLAY '---' || SUBSTR(email_domain, INSTR(email_domain, '@') );

With the two SQL Domains created, we can create a new table containing them:

create table customers (
  first_name varchar2(100), 
  last_name varchar2(100),
  cust_id number, 
  customer_voucher number domain voucher, 
  email varchar2(100) domain email_domain
  );

We are ready to insert some data:

insert into customers values ('John','Smith',1,100,'john.smith@mail.com');
insert into customers values ('Arno','Links',2,50,'arno.links@mail.com');
insert into customers values ('Becky','Graham',3,500,'becky.graham@mail.com');
insert into customers values ('Belinda','Dunlop',4,250,'belinda.dunlop@mail.com');
insert into customers values ('Frank','Hardy',5,0,'frank.hardy@mail.com');
commit;

We are going to run a simple query. You can see a new SQL function called domain_order. This function returns the domains order expression in which was defined. For the voucher domain we defined a -1. This means we want a descendent order:

SQL> select first_name,last_name,customer_voucher,email 
from customers order by domain_order(customer_voucher);

FIRST_NAME    LAST_NAME       CUSTOMER_VOUCHER EMAIL
_____________ ____________ ___________________ __________________________
Becky         Graham                       500 becky.graham@mail.com
Belinda       Dunlop                       250 belinda.dunlop@mail.com
John          Smith                        100 john.smith@mail.com
Arno          Links                         50 arno.links@mail.com
Frank         Hardy                          0 frank.hardy@mail.com

SQL>

If we remove the SQL function, we can see it is ordered ascendant:

SQL> select first_name,last_name,customer_voucher,email 
from customers order by customer_voucher;

FIRST_NAME    LAST_NAME       CUSTOMER_VOUCHER EMAIL
_____________ ____________ ___________________ __________________________
Frank         Hardy                          0 frank.hardy@mail.com
Arno          Links                         50 arno.links@mail.com
John          Smith                        100 john.smith@mail.com
Belinda       Dunlop                       250 belinda.dunlop@mail.com
Becky         Graham                       500 becky.graham@mail.com

But, what happened with our display properties? You need to use the domain_display to print the already defined property. The voucher follows the defined pattern and the email has been masked:

SQL> select domain_display(customer_voucher),domain_display(email) from customers;

DOMAIN_DISPLAY(CUSTOMER_VOUCHER)    DOMAIN_DISPLAY(EMAIL)
___________________________________ ________________________
 $100.00                            ---@mail.com
  $50.00                            ---@mail.com
 $500.00                            ---@mail.com
 $250.00                            ---@mail.com
    $.00                            ---@mail.com

SQL>

Oracle already provides many built-in usage domains so is worth it to have a look so you can take advantage of them. You can find the list on the documentation here, but I’m going to create a table containing phone numbers and a credit card using the existing domains.

create table employees (
  credit_card credit_card_number_d,
  phone phone_number_d
  );

If you want to query and understand the metadata defined, you can query some dictionary views to find the table which contains annotations:

SQL> SELECT * from USER_ANNOTATIONS_USAGE WHERE Object_Name = 'CUSTOMERS' AND Object_Type = 'TABLE';

OBJECT_NAME    OBJECT_TYPE    COLUMN_NAME         DOMAIN_NAME    DOMAIN_OWNER    ANNOTATION_NAME    ANNOTATION_VALUE
______________ ______________ ___________________ ______________ _______________ __________________ ________________________________________________________________________
CUSTOMERS      TABLE          CUSTOMER_VOUCHER    VOUCHER        TEST            PROPERTIES         {"Department": "Marketing", "Country": "USA", "Max Voucher": "500$" }

SQL>

We have seen a good overview of the SQL Domains capabilities, but there are more! We can use them to define JSON schemas! We can validate the structure and type information of your JSON documents.

create  domain product_schema as JSON validate using 
'{
    "type": "object", 
    "additionalProperties":false,
    "required":["starring"],
    "properties": {
    "id": {"type": "number"} ,
    "type" : {"type":"string","maxLength": 50},
    "title" : {"type":"string","maxLength": 50},
    "format" : {"type":"string","maxLength": 50},
    "condition" : {"type":"string","maxLength": 50},
    "price": {"type": "number"},
    "comment" : {"type":"string","maxLength": 50},
    "starring" : {"type":"array"},
    "year": {"type": "number"} ,
    "decade" : {"type":"string","maxLength": 50}
    }
    }';

In the product_schema domain you can see the different properties of your JSON schema. We have added to important fields. The first one is required. If the starring array is not present, it will raise an error when trying to insert new data. Also we have the additionalProperties field, which is not allowing new fields into the JSON.

Once we have the schema defined, we can create the new table directly:

CREATE TABLE products(json_data JSON DOMAIN product_schema);

Let’s try the first insert. This one is following the schema so we are not getting any error.

insert into products(json_data) values(
        '{
          "id": 100,
          "type": "movie",
          "title": "Coming to America",
          "format": "DVD",
          "condition": "acceptable",
          "price": 5,
          "comment": "DVD in excellent condition, cover is blurred",
          "starring": [
            "Eddie Murphy",
            "Arsenio Hall",
            "James Earl Jones",
            "John Amos"
          ],
          "year": 1988,
          "decade": "80s"
        }
        ');
        commit;

The next JSON document is missing the starring field. Therefore we are getting an error.

insert into products(json_data) values(
          '{
            "id": 100,
            "type": "movie",
            "title": "Coming to America",
            "format": "DVD",
            "condition": "acceptable",
            "price": 5,
            "comment": "DVD in excellent condition, cover is blurred",
            "year": 1988,
            "decade": "80s"
          }
          ')
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-40875: JSON schema validation error

In this last example we are adding a new field called pages, and also we are getting an error.

insert into Products(json_data) values(
            '{
              "id": 103,
              "pages": 250,
              "title": "The Thing",
              "type": "book",
              "condition": "okay",
              "price": 2.5,
              "author": "Alan Dean Forster",
              "starring": [
                "Eddie Murphy",
                "Arsenio Hall",
                "James Earl Jones",
                "John Amos"
              ],
              "year": 1982,
              "decade": "80s"
            }
            ')
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-40875: JSON schema validation error

In this blog we had covered SQL Domains, annotations and JSON Schemas. If you want to have a deeper look you can find the Oracle Database 23c Developer free doc here