Oracle plsql: how lo load json file into nested tables

Question

Using oracle plsql, please tell me how to use JSON_TABLE, bfilename and "bulk collect" in order to read data from a json file.

The json file contains 2 lists of strings:

{ 
  "included_errors": [ "include1", "include2" ],
  "excluded_errors": [ "exclude1", "exclude2" ]
}

Without using intermediate tables, I want to load both string lists into 2 list variables which are nested tables, i.e.:

TYPE list_t IS TABLE OF VARCHAR2(100);

l_included_errors list_t;
l_excluded_errors list_t;

Then I will traverse the lists in a for loop.

Answer 1

I ended doing the following, which works:

DECLARE
  TYPE T_STRINGS IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
  include_list T_STRINGS;
  exclude_list T_STRINGS;
  json_file    BFILE;
BEGIN
  json_file := BFILENAME('MY_DIRECTORY', 'my_file.json');

  SELECT my_data
    BULK COLLECT
    INTO include_list
    FROM JSON_TABLE(json_file,
                    '$.included_errors[*]'
                    COLUMNS(my_data VARCHAR2(1000) PATH '$'));

  SELECT valor
    BULK COLLECT
    INTO exclude_list
    FROM JSON_TABLE(json_file,
                    '$.excluded_errors[*]'
                    COLUMNS(valor VARCHAR2(1000) PATH '$'));

  -- process both lists here
  NULL;
END;

Answer 2

You can use:

DECLARE
  TYPE list_t IS TABLE OF VARCHAR2(100);
  v_included list_t;
  v_excluded list_t;
  v_json     CLOB;
BEGIN
  -- Insert your logic to load from file here
  v_json  := '{ 
  "included_errors": [ "include1", "include2" ],
  "excluded_errors": [ "exclude1", "exclude2" ]
}';

  SELECT value
  BULK COLLECT INTO v_included
  FROM   JSON_TABLE(
           v_json,
           '$.included_errors[*]'
           COLUMNS(
             value VARCHAR2(100) PATH '$'
           )
         );

  SELECT value
  BULK COLLECT INTO v_excluded
  FROM   JSON_TABLE(
           v_json,
           '$.excluded_errors[*]'
           COLUMNS(
             value VARCHAR2(100) PATH '$'
           )
         );

  DBMS_OUTPUT.PUT_LINE('Included:');
  FOR i IN 1 .. v_included.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_included(i));
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Excluded:');
  FOR i IN 1 .. v_excluded.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_excluded(i));
  END LOOP;
END;
/

Which outputs:

Included:
include1
include2
Excluded:
exclude1
exclude2

fiddle