2021 – JSON_TRANSFORM And Custom PL/SQL

My prior posts on JSON_TRANSFORM used simple text values and built-in Oracle functions like SYSDATE. This post demonstrates calling custom PL/SQL functions with JSON_TRANSFORM.

Here are the two rows of demo data for this post. The pertinent keys are pname (patient name) and OS (Ordering System) in the elements of the mrns array.

SELECT JSON_VALUE (patient_data,'$.pname') pname,
       JSON_QUERY (patient_data,'$.mrns[*].OS' WITH WRAPPER) os
  FROM json_patient;
pname     os
--------- -----
Patient 1 [1,2]
Patient 2 [1,2]

The business requirement is that each patient only have one of each OS in its mrns array. The two rows of demo data meet this requirement. I wrote a function named OS_LIST_UNIQUE to check this requirement and return 1 if unique or 0 if not.

Calling A Function

Here is a JSON_TRANSFORM INSERT adding the unique OS check.

SELECT JSON_VALUE(x,'$.pname') pname,
       JSON_VALUE(x,'$.unique_os') os_unique
  FROM ( ----
         SELECT JSON_TRANSFORM(patient_data,
                  INSERT '$.unique_os' = 
               OS_LIST_UNIQUE( JSON_QUERY(patient_data,
                                 '$.mrns[*].OS' WITH WRAPPER) ) ) x
           FROM json_patient
         -----
        );
pname os_unique
--------- ---------
Patient 1 1
Patient 2 1

It returns the unique indicator (1 or 0) as the new unique_os key at the top of the JSON tree.

Thanks for reading!