Hi Thomas,
Great to hear that you got it working!
I still doubt that you are missing any characters in the input file but that
you merely got a file that includes multiple JSON documents one per line.
Wrapping it around [ ] and with commas in the end of each line essentially
transforms the file to have one JSON document that happens to be an array and
hence JSON_TABLE works.
In a simple example, imagine you have two standalone simple JSON documents:
{"name": "Gerald"}
{"name": "Kris"}
As said, JSON_TABLE expects to retrieve one document as first parameter, not
multiple.
So if you try to pass the entire value onto JSON_TABLE, it will not work:
SQL> select *
from json_table(
'{"name": "Gerald"}
{"name": "Kris"}',
'$[*]'
COLUMNS (name VARCHAR2(10)));
NAME
----------
Gerald
When you wrap it in [ ] and put a comma at the end, however, you now made the
two documents a new single document with two entries:
[{"name": "Gerald"},
{"name": "Kris"}]
In a pretty-printed textual representation this document would look more like
this, which makes it more obvious perhaps:
[
{
"name": "Gerald"
},
{
"name": "Kris"
}
]
Now the query works as expected because JSON_TABLE receives just this single
document as input:
SQL> select *
from json_table(
'[{"name": "Gerald"},
{"name": "Kris"}]',
'$[*]'
COLUMNS (name VARCHAR2(10)));
NAME
----------
Gerald
Kris
You can use this workaround, of course, but it means you will have to modify
the files and if you get a big file of several GBs, you may run into PGA memory
constraints.
The way to use an external table with it would look like this:
SQL> SELECT *
FROM
EXTERNAL (
(
jdata CLOB
)
TYPE oracle_loader
DEFAULT DIRECTORY JSON_DATA
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
)
LOCATION ('test.txt')
REJECT LIMIT UNLIMITED
) ext_json,
JSON_TABLE(ext_json.jdata, '$[*]'
COLUMNS(
name VARCHAR2(10)
)
);
JDATA
--------------------------------------------------------------------------------
NAME
----------
{"name":"Gerald"}
Gerald
{"name":"Kris"}
Kris
There is two components on the top in the FROM clause, the EXTERNAL one and
JSON_TABLE.
JSON_TABLE is basically the same as before, the EXTERNAL uses the Inline
External Table functionality (no more need to create an external table, but you
still can, of course, if you prefer).
The important parameters there are the “RECORDS DELIMITED BY NEWLINE” which
will generate a row per line in the file and the LOCATION which specifies the
file name
Just selecting from the external table directly demonstrates that:
SQL> SELECT *
FROM
EXTERNAL (
(
jdata CLOB
)
TYPE oracle_loader
DEFAULT DIRECTORY JSON_DATA
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
)
LOCATION ('test.txt')
REJECT LIMIT UNLIMITED
) ext_json,
JSON_TABLE(ext_json.jdata, '$[*]'
COLUMNS(
name VARCHAR2(10)
)
);
JDATA
--------------------------------------------------------------------------------
{"name":"Gerald"}
{"name":"Kris"}
Wrapping it all up, to borrow Sayan’s example:
SQL> SELECT jt.*
FROM
EXTERNAL (
(
jdata CLOB
)
TYPE oracle_loader
DEFAULT DIRECTORY JSON_DATA
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
FIELDS TERMINATED BY 'XXX'
)
LOCATION ('D_20221122.txt')
REJECT LIMIT UNLIMITED
) ext_json,
JSON_TABLE(
ext_json.jdata,
'$[*]'
Columns(
N int,
Nested path '$.cData[*]'
columns(
cCode varchar2(5) path '$.cCode[*]'
)
)
) jt;
N CCODE
---------- -----
1 123
1 345
1 456
2 100
2 200
2 300
6 rows selected.
Note that I have added here the FIELDS TERMINATED BY ‘XXX’.
I’ve done that as per default the external table would use a comma as the field
separator, but as we are dealing with JSON here, we want the entire row as a
single field.
Hence I passed on a termination value ‘XXX’ that is unlikely to exist in the
data itself and hence the entire row is returned.
With this statement you will not need to change any data inside the file and it
will also easily deal with large files as well.
Hope this helps!
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 29, 2022, at 06:39, Terrian, Thomas J CTR DLA INFO OPERATIONS (USA)
<Tom.Terrian.ctr@xxxxxxx<mailto:Tom.Terrian.ctr@xxxxxxx>> wrote:
Sayan, success!!!
I think the json file that I received was missing some characters…I put [] at
the start and end of the file and commas between each line. It works now!!
Thanks for your assistance.
From: Terrian, Thomas J CTR DLA INFO OPERATIONS (USA)
Sent: Tuesday, November 29, 2022 6:08 AM
To: xt.and.r@xxxxxxxxx<mailto:xt.and.r@xxxxxxxxx>;
gerald.venzl@xxxxxxxxxx<mailto:gerald.venzl@xxxxxxxxxx>
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Subject: RE: [URL Verdict: Neutral][Non-DoD Source] Re: [External] : JSON
Awesome…I will give it another try this morning.
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> On Behalf
Of Sayan Malakshinov
Sent: Monday, November 28, 2022 6:47 PM
To: gerald.venzl@xxxxxxxxxx<mailto:gerald.venzl@xxxxxxxxxx>
Cc: Terrian, Thomas J CTR DLA INFO OPERATIONS (USA)
<Tom.Terrian.ctr@xxxxxxx<mailto:Tom.Terrian.ctr@xxxxxxx>>; oracle-l
<oracle-l@xxxxxxxxxxxxx<mailto: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: gerald.venzl@xxxxxxxxxx<mailto: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<http://orasql.org/>
