RE: [External] : JSON

Thanks.Oracle 19.  

I tried setting up an external table with the JSON file as the source. I couldn't figure out the syntax. I will try again tomorrow.  

From: Gerald Venzl <gerald.venzl_at_oracle.com> Sent: Monday, November 28, 2022 2:24 PM
To: Terrian, Thomas J CTR DLA INFO OPERATIONS (USA)
<Tom.Terrian.ctr_at_dla.mil>

Cc: oracle-l <oracle-l_at_freelists.org> Subject: [URL Verdict: Neutral][Non-DoD Source] Re: [External] : JSON  

JSON_TABLE will see the entire content of the text file as just one record.

You need to tell the DB that the file contains multiple records separated by X, probably a new line.  

Instead of using BFILENAME, the better approach is probably an External Table over the file, see:
https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/loading- external-json-data.html#GUID-52EFC452-5E65-4148-8070-1FA588A6E697  

Depending on which DB version you are, you can probably just use an Inline External Table definition:
https://oracle-base.com/articles/18c/inline-external-tables-18c  

Thanks,  

Gerald Venzl | Senior Director | Product Management Email: <mailto:gerald.venzl_at_oracle.com> gerald.venzl_at_oracle.com Oracle ST & Database Development
400 Oracle Parkway | Redwood Shores | 94065 | USA

On Nov 28, 2022, at 12:14, Terrian Thomas J CTR DLA INFO OPERATIONS
<dmarc-noreply_at_freelists.org <mailto:dmarc-noreply_at_freelists.org> > wrote:
 

Anyone know how to load a JSON file into a database?  

When I run:  

select *

from json_table(

bfilename('JSON_DATA', 'D_20221122.txt'),

'$[*]'

Columns(

Nested path '$.cData[*]'

columns(

cCode varchar2(5) path '$.cCode[*]'

)

                                                )

);  

I get only 1 record:  

CCODE

7MDQ6   How can I see all of the records in the file (JSON_DATA/'D_20221122.txt)?  

--
http://www.freelists.org/webpage/oracle-l




application/pkcs7-signature attachment: smime.p7s