2021 – Virtual Columns For JSON Access Part 3

The previous posts in this series here and here explain the details of adding a Virtual Column (VC) to a table containing JSON data. The VC parsed the JSON to assemble a unique list of values.

This post changes that VC to determine if the list passed in is unique and then return 1 if it is and 0 if it is not.

Here is the function the VC will be built on.

CREATE OR REPLACE FUNCTION os_list_unique ( p_os_list VARCHAR2 )
                   RETURN NUMBER
                   DETERMINISTIC
                   RESULT_CACHE AS

  v_local_list VARCHAR2(1000);
  v_item_count BINARY_INTEGER;
  v_array DBMS_UTILITY.LNAME_ARRAY;
  TYPE v_tab_t IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
  v_tab v_tab_t;
  v_lmnt VARCHAR2(100);

BEGIN

  -- remove the square brackets from the JSON object passed in
  v_local_list := REPLACE(p_os_list,'[','');
  v_local_list := REPLACE(v_local_list,']','');

  -- add x's so the table can load correctly and load the table
  v_local_list := regexp_replace(v_local_list,'(^|,)','\1x');
  DBMS_UTILITY.COMMA_TO_TABLE(v_local_list,v_item_count,v_array);

  -- loop through recording the unique values only
  FOR counter IN 1..v_item_count LOOP
    v_tab(v_array(counter)) := 1;
  END LOOP;

  -- now loop through the list of unique values and add them
  v_lmnt := v_tab.FIRST;
  v_local_list := '';
  LOOP
    EXIT WHEN v_lmnt IS NULL;
    v_local_list := v_local_list || v_lmnt || ',';
    v_lmnt := v_tab.NEXT(v_lmnt);
  END LOOP;

  -- remove the last comma and the x's
  v_local_list := RTRIM(v_local_list,',');
  v_local_list := REPLACE(v_local_list,'x','');
  v_local_list := '[' || v_local_list || ']';

  IF v_local_list = p_os_list THEN
    RETURN(1);
  ELSE
    RETURN(0);
  END IF;

END;

Now test the function with some hardcoded data.

BEGIN
  -- list is unique so return 1
  DBMS_OUTPUT.PUT_LINE(os_list_unique('[1,2,3]'));
  -- list is not unique so return 0
  DBMS_OUTPUT.PUT_LINE(os_list_unique('[1,4,1]'));
END;
1
0

Then query the function from the patient table.

SELECT JSON_VALUE(patient_data,'$.pname') pname,
        JSON_QUERY(patient_data,'$.mrns[*].OS' WITH WRAPPER) os_list,
        os_list_unique(JSON_QUERY(patient_data,'$.mrns[*].OS' WITH WRAPPER)) unq
  FROM json_patient

PNAME     OS_LIST UNQ
--------- ------- ---
Patient 1 [1,2]     1
Patient 2 [1,1]     0

Add the virtual column.

ALTER TABLE json_patient
 ADD ( unique_os NUMBER(1) GENERATED ALWAYS
    AS (os_list_unique(JSON_QUERY(patient_data,'$.mrns[*].OS' WITH WRAPPER))) VIRTUAL);

Query the virtual column.

SELECT JSON_VALUE(patient_data,'$.pname') pname,
       JSON_QUERY(patient_data,'$.mrns[*].OS' WITH WRAPPER) os_list,
       unique_os
  FROM json_patient;
PNAME     OS_LIST UNIQUE_OS
--------- ------- ---------
Patient 1 [1,2]           1
Patient 2 [1,1]           0

Thanks for reading!

Leave a Comment