RE: [URL Verdict: Neutral][Non-DoD Source] [External] : JSON

From: Terrian Thomas J CTR DLA INFO OPERATIONS <dmarc-noreply_at_freelists.org>
Date: Wed, 30 Nov 2022 11:22:59 +0000
Message-ID: <BN8P111MB1908132FAB1E9E9F1F1798CFB1159_at_BN8P111MB1908.NAMP111.PROD.OUTLOOK.COM>

Wow. Excellent. Thanks.

From: Gerald Venzl <gerald.venzl_at_oracle.com> Sent: Tuesday, November 29, 2022 8:39 PM To: Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <Tom.Terrian.ctr_at_dla.mil> Cc: xt.and.r_at_gmail.com; oracle-l <oracle-l_at_freelists.org> Subject: Re: [URL Verdict: Neutral][Non-DoD Source] [External] : JSON

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,

On Nov 29, 2022, at 06:39, Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <Tom.Terrian.ctr_at_dla.mil <mailto:Tom.Terrian.ctr_at_dla.mil> > 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_at_gmail.com <mailto:xt.and.r_at_gmail.com> ; gerald.venzl_at_oracle.com <mailto:gerald.venzl_at_oracle.com> Cc: oracle-l <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > Subject: RE: [URL Verdict: Neutral][Non-DoD Source] Re: [External] : JSON

Awesome…I will give it another try this morning.

From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> > On Behalf Of Sayan Malakshinov Sent: Monday, November 28, 2022 6:47 PM
To: gerald.venzl_at_oracle.com <mailto:gerald.venzl_at_oracle.com> Cc: Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <Tom.Terrian.ctr_at_dla.mil <mailto:Tom.Terrian.ctr_at_dla.mil> >; oracle-l <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > 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_at_freelists.org <mailto:dmarc-noreply_at_freelists.org> > wrote:

Happy to help if you can provide me with the file format with some dummy data :)

Thx,

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,

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

-- 

Best regards,
Sayan Malakshinov

Oracle performance tuning engineer

Oracle ACE
http://orasql.org <http://orasql.org/> 

 







--
http://www.freelists.org/webpage/oracle-l




application/pkcs7-signature attachment: smime.p7s

Received on Wed Nov 30 2022 - 12:22:59 CET

This message: [ Message body ]
Previous message: Oleksandr Denysenko: "Re: ASM Filter Driver and Exadata"
In reply to: Gerald Venzl: "Re: [URL Verdict: Neutral][Non-DoD Source] [External] : JSON"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message