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> 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_at_oracle.com
> 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_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>
> *Sent:* Monday, November 28, 2022 2:24 PM
> *To:* Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <
> Tom.Terrian.ctr_at_dla.mil>
> *Cc:* oracle-l <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,
>
> ---------------
>
> Gerald Venzl | Senior Director | Product Management
> Email: 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> 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://www.freelists.org/webpage/oracle-l