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)?
