RE: [External] : JSON
Date: Tue, 29 Nov 2022 11:45:14 +0000
Message-ID: <BN8P111MB1908BFEA285533A7A560AC7AB1129_at_BN8P111MB1908.NAMP111.PROD.OUTLOOK.COM>
Gerald,
I suspect you are right with your comment: “You need to tell the DB that the file contains multiple records separated by X, probably a new line.”
Do you have an example on how to do that with the json_table function? I can’t figure out the syntax.
From: Gerald Venzl <gerald.venzl_at_oracle.com>
Sent: Monday, November 28, 2022 6:31 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
Happy to help if you can provide me with the file format with some dummy data :)
Thx,
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 16:02, Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <Tom.Terrian.ctr_at_dla.mil <mailto:Tom.Terrian.ctr_at_dla.mil> > wrote:
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 <mailto: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 <mailto:Tom.Terrian.ctr_at_dla.mil> >
Cc: oracle-l <oracle-l_at_freelists.org <mailto: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: gerald.venzl_at_oracle.com <mailto: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-lReceived on Tue Nov 29 2022 - 12:45:14 CET
- application/pkcs7-signature attachment: smime.p7s