Augmenting JSON Relational Duality Views with generated data

A recent forum post inspired this brief article on generating summary data in JSON Relational Duality Views.

Typically you map a table’s columns to fields in the resulting JSON. If you can’t use a 1:1 mapping in your Duality View for any reason, it is possible to augment the Duality View with generated fields.

Let’s walk through an example using the Human Resources sample schema.

Default JSON Relational Duality View

What if you were required to provide a JSON Relational Duality View showing your company’s departments along with each department’s employees. In that case you could start like so:

			
create or replace force editionable json relational duality view hr.emp_dept_dv as 
hr.departments {
    _id : department_id
    departmentName : department_name
    employees : hr.employees
             @link (to : [department_id]) {
        employeeid : employee_id
        firstname : first_name
        lastname : last_name
        email : email
    }
}
;

		

So far, no aggregate/summary detail is present. The Duality View returns each department, along with the employees. Here is the output for department number 20

			
select
    json_serialize(d.data pretty) data
from
    emp_dept_dv d
where
    d.data."_id" = 20;
DATA
___________________________________________________
{
    "_id": 20,
    "departmentName": "Marketing",
    "employees": [
        {
            "employeeId": 201,
            "firstName": "Michael",
            "lastName": "Martinez",
            "email": "MMARTINE"
        },
        {
            "employeeId": 202,
            "firstName": "Pat",
            "lastName": "Davis",
            "email": "PDAVIS"
        }
    ],
    "_metadata": {
        "etag": "032AB4D0D94BCEF16ED5244B95C19901",
        "asof": "00000000002D42FB"
    }
}

		

So far, so good.

Adding summary information

Let’s assume the task is expanded to include the employees’ salaries, the number of employees by department, and the average salary of the department’s employees.

As per the documentation you can add fields to the JSON, and either calculate their values using SQL or a JSON Path Expression. Since the Duality View was initially defined using the GraphQL-like format, a JSON Path Expression has been chosen.

			
create or replace force editionable json relational duality view hr.emp_dept_dv as 
hr.departments @nocheck {
    _id : department_id
    departmentName : department_name
    employeeCount @generated (path : "@.employees.size()")
    avgSalary @generated (path : "@.employees.salary.avg()")
    employees : hr.employees
             @link (to : [department_id]) {
        employeeId : employee_id
        firstName : first_name
        lastName : last_name
        email : email
        salary
    }
}
;

		

First, add the salary field to the employees array. You can see the new field in line 13. Next, focus on summary details: lines 5 and 6 add fields for employee count and average salary. The @generated annotation lets you define a JSON path and use item methods like size() and avg() to calculate these values.

Let’s have a look at the output:

			
select
    json_serialize(d.data pretty) data
from
    emp_dept_dv d
where
    d.data."_id" = 20;
DATA
___________________________________________________
{
  "_id" : 20,
  "_metadata" :
  {
    "etag" : "3091B9948A2D43A782E1BD90FCCBB872",
    "asof" : "000000000030007E"
  },
  "departmentName" : "Marketing",
  "employees" :
  [
    {
      "employeeId" : 201,
      "firstName" : "Michael",
      "lastName" : "Martinez",
      "email" : "MMARTINE",
      "salary" : 13000
    },
    {
      "employeeId" : 202,
      "firstName" : "Pat",
      "lastName" : "Davis",
      "email" : "PDAVIS",
      "salary" : 6000
    }
  ],
  "employeeCount" : 2,
  "avgSalary" : 9500
}

		

The augmented Duality View now displays the number of employees in department 20, as well as the average salary for the entire department, too.

Summary

You can enhance JSON Relational Duality Views by adding generated fields with SQL or JSON Path Expressions. Letting the database handle these calculations often boosts performance and relieves you from having to reinvent the wheel in the application tier.

JSON