Awesome…I will give it another try this morning.
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Sayan Malakshinov
Sent: Monday, November 28, 2022 6:47 PM
To: gerald.venzl@xxxxxxxxxx
Cc: Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <Tom.Terrian.ctr@xxxxxxx>;
oracle-l <oracle-l@xxxxxxxxxxxxx>
Subject: [URL Verdict: Neutral][Non-DoD Source] Re: [External] : JSON
Hi Terrian,
Show your json file, please. I've ran my test successfully with this file:
https://gist.github.com/xtender/e4f9936a21638955d3ef3d0c12b58224
$ cat D_20221122.txt
[
{
"N":1,
"cData": [
{"cCode": "123"},
{"cCode": "345"},
{"cCode": "456"}
]
},
{
"N":2,
"cData": [
{"cCode": "100"},
{"cCode": "200"},
{"cCode": "300"}
]
}
]
SQL> select jt.*
2 from json_table(
3 bfilename('DATA_PUMP_DIR', 'D_20221122.txt'),
4 '$[*]'
5 Columns(
6 N int,
7 Nested path '$.cData[*]'
8 columns(
9 cCode varchar2(5) path '$.cCode[*]'
10 )
11 )
12 ) jt;
N CCODE
---------- -----
1 123
1 345
1 456
2 100
2 200
2 300
6 rows selected.
On Mon, Nov 28, 2022 at 11:32 PM Gerald Venzl <dmarc-noreply@xxxxxxxxxxxxx
<mailto:dmarc-noreply@xxxxxxxxxxxxx> > wrote:
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@xxxxxxxxxx> gerald.venzl@xxxxxxxxxx
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@xxxxxxx <mailto:Tom.Terrian.ctr@xxxxxxx> > 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@xxxxxxxxxx <mailto:gerald.venzl@xxxxxxxxxx> >
Sent: Monday, November 28, 2022 2:24 PM
To: Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <Tom.Terrian.ctr@xxxxxxx
<mailto:Tom.Terrian.ctr@xxxxxxx> >
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx> >
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@xxxxxxxxxx <mailto:gerald.venzl@xxxxxxxxxx>
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@xxxxxxxxxxxxx <mailto:dmarc-noreply@xxxxxxxxxxxxx> > 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)?
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org