Let’s look again at the JSON data type. Using JSON documents for applications makes it easier to view and pull in custom information such as their properties and attributes as a JSON document. JSON is an easy-to-parse data format and doesn’t need the complete schema defined.
JSON is a text format, and the components are identified within the document. It is also easy to send JSON as an API and validate the format. JSON documents are popular with developers for temporarily storing data.
Oracle 23c introduced JSON relational duality views and the JSON data type, along with the functions to verify and format JSON documents using SQL.
Talking about JSON in this way, instead of a data type or different type of view in the database, highlights more of the way applications will be using the data or development teams will be accessing the data.
You can provide them with data in a format that uses the existing relational tables in the database, and they can access the JSON documents.
Let’s say we have customer, items, and orders tables for example purposes, as shown here:
SQL> create table customer (customer_id number primary key, customer_name varchar2(80), address1 varchar2(80),city varchar2(80), state varchar2(20), zipcode number,geo_location sdo_geometry, customer_begin_date date);
Table created.
SQL> create table items (item_id number primary key, item_name varchar2(80),item_description varchar2(4000), item_price number);
Table created.
SQL> create table orders (order_id number primary key, customer_id number,item_id number, order_date date);
Table created.
SQL> alter table orders add constraint fk_customer foreign key (customer_id) references customer(customer_id);
SQL> alter table orders add constraint fk_items foreign key (item_id) references items(item_id);–need to have foreign key references for creating duality views.
These tables were created for taking orders, and then the data needs to be passed off to print invoices, ship products, or other processes for these orders.
The different systems can take the data in JSON documents, so all of the information is provided at once to give the needed details. If the transaction data was not stored as relational tables, and only as JSON, I might have duplicate data that would need to be kept regarding items and customers. Changes to items would have to update different documents for orders, which might cause data consistency issues.
The relational tables handle this. The JSON relational duality view on top of these tables provides the JSON document with real-time consistent data. Updates, inserts, and deletes can also be handled through these views.
Here is a sample JSON relational duality view, and you can create several other views depending on the needed attributes and uses. Since it does not store the data, just the metadata, it makes it easy to create a view that is designed for the use case:
SQL> create json relational duality view cust_orders_dv as select JSON {‘customer_id’ is c.customer_id, ‘customer_name’ is c.customer_name,’order’ is [select JSON {‘order_id’ is o.order_id, ‘order_date’ is o.order_date,’item_id’ is o.item_id } from orders owhere c.customer_id=o.customer_id]} from customer c;
Maybe there was a shipped column that allows for changes through the view to update that the order has been shipped. We did discuss this topic before with views, but it is important to mention it again when discussing data management.
This is just to start to get the ideas flowing and recognize when the development teams are looking for the JSON format, there are ways to do just that.
Besides the relational tables and JSON views, we can do analytics on the data and include JSON in machine learning and graph algorithms.