Up to now the examples of previous blogs inserted JSON data into JSON enabled columns of tables. This blog shows how JSON data can be created or derived automatically, and, more importantly, how relational data can be transformed and represented automatically as hierarchical JSON data.
Virtual Columns
A virtual column of a table in Oracle is defined by referring to a function invocation. The value of the column is the function invocation result. Parameters (column values of the same table) can be passed to the function and so the functionality of the function can be dependent on the data content of the row.
A simple example is shown in the following. A virtual column is created that contains a name – value pair based on a non-virtual column of the same table (the function is string concatenation).
DROP TABLE vc;
CREATE TABLE vc
(
id NUMBER NOT NULL,
name VARCHAR(255),
value generated always AS (
'"name"' || ':' || '"' || name || '"' ));
INSERT INTO vc (id, name) VALUES (1, 'abc');
INSERT INTO vc (id, name) VALUES (2, 'def');
SELECT * FROM vc;
Value Column with JSON Structure
The following example shows a similar example, however, in this case the virtual column is a JSON column and the contents is a JSON object containing the name – value pair. The table definition ensures that the column is a JSON column and that it can be accessed with the JSON functions.
DROP TABLE vc;
CREATE TABLE vc
(
id NUMBER NOT NULL,
name VARCHAR(255),
value generated always AS (
'{' || '"name"' || ':' || '"' || name || '"' || '}')
virtual CONSTRAINT value_ensure_json
CHECK (value IS JSON (STRICT WITH UNIQUE KEYS)) );
INSERT INTO vc (id, name) VALUES (1, 'abc');
INSERT INTO vc (id, name) VALUES (2, 'def');
SELECT * FROM vc;
Separate Function creating JSON Structure
Functions can be created separately as first class citizens in the database (for the purpose of proper engineering practices as well as functionality reuse); they do not necessarily have to be inline invocations. The following example creates a separate function that creates a JSON object containing a name – value pair.
CREATE OR REPLACE FUNCTION
to_JSON(in_name IN VARCHAR2,
in_value IN VARCHAR2)
RETURN VARCHAR2 deterministic
IS
JSON_value VARCHAR(255);
BEGIN
JSON_value :=
'{' || '"' || in_name || '"' || ':' ||
'"' || in_value || '"' || '}';
RETURN JSON_value;
END;
DROP TABLE vc;
CREATE TABLE vc
(
id NUMBER NOT NULL,
name VARCHAR(255),
value generated always AS (
to_JSON('name', name))
virtual CONSTRAINT value_ensure_json
CHECK (value IS JSON (STRICT WITH UNIQUE KEYS)));
INSERT INTO vc (id, name) VALUES (1, 'abc');
INSERT INTO vc (id, name) VALUES (2, 'def');
SELECT * FROM vc;
Function with Selection
Functions have the full range of SQL available and can execute queries in their implementation. The following example queries a second table and creates a JSON object from the result. Note that the query depends on the input parameter.
CREATE OR REPLACE FUNCTION
from_dep(in_value IN VARCHAR2)
RETURN VARCHAR2 deterministic
IS
JSON_value VARCHAR(255);
dep_value VARCHAR(255);
BEGIN
EXECUTE IMMEDIATE 'SELECT ' || ' adr ' ||
' FROM ' || ' dep '
INTO dep_value;
JSON_value := '{' || '"' || in_value || '"' || ':' ||
'"' || dep_value || '"' || '}';
RETURN JSON_value;
END;
DROP TABLE vc;
CREATE TABLE vc
(
id NUMBER NOT NULL,
name VARCHAR(255),
value generated always AS
(from_dep(name))
virtual CONSTRAINT value_ensure_json
CHECK (value IS JSON (STRICT WITH UNIQUE KEYS)) );
DROP TABLE dep;
CREATE TABLE dep
(
adr VARCHAR(255));
INSERT INTO dep VALUES ('yahoo');
INSERT INTO vc (id, name) VALUES (1, 'abc');
INSERT INTO vc (id, name) VALUES (2, 'def');
SELECT * FROM vc;
Hierarchical JSON Column
The last example shows how a function can create complex hierarchical JSON structures. This allows for denormalizing a relational model into a JSON document representation as needed. This is the use case where a client requires a self-contained JSON object that contains all necessary data so that the client does not have to perform repeated queries to obtain all data.
CREATE OR REPLACE FUNCTION
from_dep(v_id IN NUMBER)
RETURN VARCHAR2 deterministic
IS
JSON_value VARCHAR(255);
dep_value VARCHAR(255);
CURSOR dep_cur
IS
SELECT * FROM dep d WHERE d.vc = v_id;
l_dep_adr dep%ROWTYPE;
is_first NUMBER;
BEGIN
is_first := 0;
JSON_value := '[';
OPEN dep_cur;
LOOP
FETCH dep_cur INTO l_dep_adr;
EXIT
WHEN dep_cur%NOTFOUND;
IF (is_first = 0)
THEN
JSON_value := JSON_value ||
'{' || '"' || 'adr' || '"' || ':' ||
'"' || l_dep_adr.adr || '"' || '}';
is_first := 1;
ELSE
JSON_value := JSON_value || ',' ||
'{' || '"' || 'adr' || '"' || ':' ||
'"' || l_dep_adr.adr || '"' || '}';
END IF;
END LOOP;
CLOSE dep_cur;
JSON_value := JSON_value || ']';
RETURN JSON_value;
END;
DROP TABLE vc;
CREATE TABLE vc
(
id NUMBER NOT NULL,
name VARCHAR(255),
value generated always AS
(from_dep(id))
virtual CONSTRAINT value_ensure_json
CHECK (value IS JSON (STRICT WITH UNIQUE KEYS)) );
DROP TABLE dep;
CREATE TABLE dep
(
vc NUMBER, adr VARCHAR(255));
INSERT INTO vc (id, name) VALUES (1, 'abc');
INSERT INTO vc (id, name) VALUES (2, 'def');
INSERT INTO dep VALUES (1, 'yahoo');
INSERT INTO dep VALUES (2, 'yahaa');
INSERT INTO dep VALUES (2, 'yahee');
SELECT * FROM vc;
Summary
Virtual columns are an interesting mechanism when it comes to denormalizing a relational data model into a hierarchical JSON document model. This blog gave some examples upon which more complex functionality can be built. It shows that Oracle 12c can not only query JSON, but also generate JSON structures from relational data supporting a large array of use cases.
Go SQL!
Disclaimer
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
r e a l - p r o g r a m m e r 