Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

External table reading JSON with objects in array

User_33FMVNov 14 2020

Hello
I've got a json file that's structured as per the attached
json_array_objects.txt (125 Bytes)So there are individual objects within an array. Using the external table syntax that's shared in numerous examples, it can't identify the separate rows - which I completely get. It's simply looking for a line feed as a delimiter.

create or replace directory example_dir as 'E:\'

  CREATE TABLE json_array_objects
   (   JSON_DOCUMENT CLOB
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY example_dir
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY 0x'0A'
                            DISABLE_DIRECTORY_LINK_CHECK
                            FIELDS (json_document CHAR(5000)))
      LOCATION
       ( example_dir:'json_array_objects.txt'
       )
    )
   REJECT LIMIT UNLIMITED
  PARALLEL;

select * from json_array_objects

image.png
If I reformat the json file as per the other attachment - so put the json on a single line, remove the commas etc, this external table specification works as expected.

<a href="https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/A5OWWN22TWL3/json-nonarray-objects.txt" alt="json_nonarray_objects.txt type="text/plain">json_nonarray_objects.txt</a> (46 Bytes)CREATE TABLE json_nonarray_objects
 ( JSON_DOCUMENT CLOB
 )
 ORGANIZATION EXTERNAL
  ( TYPE ORACLE_LOADER
    DEFAULT DIRECTORY example_dir
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY  0x'0A'
                          DISABLE_DIRECTORY_LINK_CHECK
                          FIELDS (json_document CHAR(5000)))
    LOCATION
     ( example_dir:'json_nonarray_objects.txt'
     )
  )
 REJECT LIMIT UNLIMITED

select JSON_DOCUMENT from json_nonarray_objects

image.pngThe thing I'm struggling to understand is how I can set up the sql*loader syntax to load the first file such that it looks like the output from the second file.
I'm guessing this might involve some pre-process which is doable, but I'd rather avoid it if I can - the files are large and there's going to be lots of them. Unfortunately I don't have a great deal of control over the source format.
Any thoughts/suggestions?
Cheers
David

This post has been answered by Paulzip on Nov 14 2020
Jump to Answer
Comments
Post Details
Added on Nov 14 2020
5 comments
2,245 views