Read-Only Views Based On JSON Generation

25.7 Read-Only Views Based On JSON Generation

You can create read-only views using JSON generation functions or constructor JSON. Anyone with access can use the views as if they were read-only tables. Users of the JSON data need not know or care whether it is stored as such or generated as needed.

Example 25-13 illustrates this. The resulting view can be used as if it were a read-only table with columns ID (a department identification number) and DATA (JSON data for the department, including its employees). Using SQL*Plus command describe shows this:

describe department_view;
Name   Null?      Type
---------------------------
ID     NOT NULL   NUMBER(4)
DATA              JSON

Column DATA is of JSON data type, since the special JSON constructor syntax is used (JSON {…}). The underlying stored data comes from HR sample-schema HR, tables DEPARTMENT, LOCATION, EMPLOYEES, and JOBS.

Each row of the view provides information for single department: its ID, name, address, and employees. The address of a department is a JSON object.

The data for the employees of a department is an array of employee objects, each of which has the employee's ID, full name, and job title. The name is constructed from column data that stores the first and last names separately.

Querying the view evaluates the SQL code that invokes the JSON generation functions. Example 25-14 shows a query that returns a single document, for department 90.

To improve read performance you can materialize the view.

Because JSON-generation views are read-only, you cannot update them (unless you use INSTEAD OF triggers^Foot 1). If you need an updatable view that provides JSON data then you can create a JSON-relational duality view.

Example 25-13 Creating a View That Uses JSON Generation

CREATE VIEW department_view AS 
  SELECT  dep.department_id id,
          JSON {'id'                : dep.department_id,
                'departmentName'    : dep.department_name,
                'departmentAddress' : JSON {'street'  : loc.street_address,
                                            'zip'     : loc.postal_code,
                                            'city'    : loc.city,
                                            'state'   : loc.state_province,
                                            'country' : loc.country_id},
                'employees'         : [ SELECT
                                          JSON {'id'    : emp.employee_id,
                                                'name'  : emp.first_name || ' ' || emp.last_name,
                                                'title' : (SELECT job_title 
                                                             FROM jobs job 
                                                             WHERE job.job_id = emp.job_id)}
                                          FROM employees emp 
                                          WHERE emp.department_id = dep.department_id ]} data
    FROM departments dep, locations loc
    WHERE dep.location_id = loc.location_id;

Example 25-14 JSON Document Generated From DEPARTMENT_VIEW

This example pretty-prints the JSON document that is generated for department 90. Note the embedded objects (field departmentAddress and elements of array employees) that correspond to the subqueries used in the CREATE VIEW statement of Example 25-13.

SELECT json_serialize(data pretty) FROM department_view WHERE id = 90;

{"id"                : 90,
 "departmentName"    : "Executive",
 "departmentAddress" : {"street"  : "2004 Charade Rd",
                        "zip"     : "98199",
                        "city"    : "Seattle",
                        "state"   : "Washington",
                        "country" : "US"},
 "employees"         : [ {"id"    : 100,
                          "name"  : "Steven King",
                          "title" : "President"},
                         {"id"    : 101,
                          "name"  : "Neena Kochhar",
                          "title" : "Administration Vice President"},
                         {"id"    : 102,
                          "name"  : "Lex De Haan",
                          "title" : "Administration Vice President"} ]}