Re: [External] : JSON

From: Gerald Venzl <"Gerald>
Date: Mon, 28 Nov 2022 19:24:19 +0000
Message-ID: <CABB00D9-8CD3-4968-9256-80A6CC3540A5_at_oracle.com>

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-l

Received on Mon Nov 28 2022 - 20:24:19 CET