I am having an issue getting data from a JSON array attribute inside some JSON data which is stored in a CLOB column. I am working in an Oracle 19 database (some of the queries were modified to obfuscate the system I am working on).

The raw JSON (before loading into CLOB) looks like this:

[
    {
        "displayName":  "McGuff, Bubba",
        "ismemberof":  [
                           "cn=Active User,ou=roles,ou=currentstate,ou=MyApp,ou=IBM,o=api",
                           "cn=Base Environmental Coordinators,ou=roles,ou=currentstate,ou=MyApp,ou=IBM,o=api"
                       ],
        "employeeType":  "Worker"
    },
    {
        "displayName":  "Grumpy, Maurice",
        "ismemberof":  "cn=Active User,ou=roles,ou=currentstate,ou=MyApp,ou=IBM,o=api",
        "employeeType":  "Manager"
    },
    {
        "displayName":  "Syrimon, Quentin",
        "ismemberof":  null,
        "employeeType":  "HR"
    }
]

I first tried this query to retrieve the data:

SELECT j.process_id, 
       jt.display_Name, 
       jt.is_member_of,
       jt.employee_Type 
FROM   My_Process j
       cross apply JSON_TABLE(j.My_DATA, '$[*]'
         COLUMNS (display_Name                  VARCHAR2(200 CHAR)  PATH '$.displayName',
                  is_Member_Of                  VARCHAR2(4000 CHAR) PATH '$.ismemberof[*]',
                  employee_Type                 VARCHAR2(200 CHAR)  PATH '$.employeeType')) jt
where process_id =85
order by j.process_id;

This is what is returned:

PROCESS_ID   DISPLAY_NAME     IS_MEMBER_OF   EMPLOYEE_TYPE
85           McGuff, Bubba                   Worker
85           Grumpy, Maurice  cn=Active User,ou=roles,ou=currentstate,ou=MyApp,ou=IBM,o=api 
  Manager
85           Syrimon, Quentin                HR

So the only person that is showing an IS_MEMBER_OF attribute is Maurice. There should be a value for Bubba also.

I looked around and found this function that I thought would help but it didn't

create or replace FUNCTION json_array_to_string_tbl ( 
    p_json_array IN VARCHAR2 
    ) RETURN string_tbl_t 
    is 
      l_string_tbl string_tbl_t:= string_tbl_t(); 
    begin 
       if p_json_array is not null and length(p_json_array)>0 
       then 
           SELECT value 
           bulk collect into l_string_tbl 
           FROM json_table( p_json_array, '$[*]' 
                            COLUMNS (value PATH '$' 
                           ) 
          );  
       end if; 
       return l_string_tbl; 
    end json_array_to_string_tbl;

I then used it in this query:

SELECT j.process_id, 
       jt.display_Name, 
       json_array_to_string_tbl(jt.is_member_of) as isMemberOf,
       jt.employee_Type 
FROM   My_Process j
       cross apply JSON_TABLE(j.My_DATA, '$[*]'
         COLUMNS (display_Name                  VARCHAR2(200 CHAR)  PATH '$.displayName',
                  is_Member_Of                  VARCHAR2(4000 CHAR) PATH '$.ismemberof[*]',
                  employee_Type                 VARCHAR2(200 CHAR)  PATH '$.employeeType')) jt
where process_id =85
order by j.process_id;

The result for the Process_ID, Display_Name, and Employee_Type were the same. The IsMemberOf showed (DATASET) as the value. When I double-clicked on (DATASET), it showed an empty grid. So no values were returned.

What is the best way to retrieve the values from the IsMemberOf attribute when it is both a JSON Array and when it is just a single string.

Thanks

CodePudding user response：

The best solution is to fix your JSON so that ismemberof always contains an array.

A secondary solution is to extract both the string and array values of ismemberof and then use COALESCE to combine them into a value that is always a JSON array:

SELECT j.process_id, 
       jt.display_name,
       jt.employee_type,
       m.is_member_of
FROM   My_Process j
       CROSS APPLY JSON_TABLE(
         j.My_DATA,
         '$[*]'
         COLUMNS (
           display_Name     VARCHAR2(200 CHAR) PATH '$.displayName',
           is_Member_Of_arr CLOB FORMAT JSON   PATH '$.ismemberof',
           is_Member_Of_str CLOB               PATH '$.ismemberof',
           employee_Type    VARCHAR2(200 CHAR) PATH '$.employeeType'
         )
       ) jt
       OUTER APPLY JSON_TABLE(
         COALESCE(jt.is_member_of_arr, '["' || jt.is_member_of_str || '"]'),
         '$[*]'
         COLUMNS(
           is_member_of CLOB PATH '$'
         )
       ) m
where process_id =85
order by j.process_id;

Which, for your sample data:

CREATE TABLE My_Process (
  process_id NUMBER,
  my_data CLOB CHECK (my_data IS JSON)
);

INSERT INTO My_Process (process_id, my_data) VALUES (85, '[
    {
        "displayName":  "McGuff, Bubba",
        "ismemberof":  [
                           "cn=Active User,ou=roles,ou=currentstate,ou=MyApp,ou=IBM,o=api",
                           "cn=Base Environmental Coordinators,ou=roles,ou=currentstate,ou=MyApp,ou=IBM,o=api"
                       ],
        "employeeType":  "Worker"
    },
    {
        "displayName":  "Grumpy, Maurice",
        "ismemberof":  "cn=Active User,ou=roles,ou=currentstate,ou=MyApp,ou=IBM,o=api",
        "employeeType":  "Manager"
    },
    {
        "displayName":  "Syrimon, Quentin",
        "ismemberof":  null,
        "employeeType":  "HR"
    }
]');

Outputs:

fiddle