Oracle Parse JSON Variable into table

Question

I need to write a procedure which will accept a parameter of type CLOB, which will actually be a JSON string of text, parse that, and insert it into a table. The fields in the JSON are in the same order as the columns in the table.

The string would look like this:

{
    "signal_id": "1",
    "ts_id": "3",
    "add_price": "0",
    "qty": "1",
    "stops": "0.00",
    "yield": "0.00",
    "close_date": "NULL",
    "close_price": "0.00",
    "ticker": "IBM",
    "option_ticker": "NULL",
    "signal_date": "2012-07-25",
    "estimated_reporting_date": "NULL",
    "signal_val": "1",
    "comp_name": "INTERNATIONA",
    "lt_price": "190.34",
    "sell_target": "NULL",
    "high_target": "NULL",
}

What is the best way to parse that, and insert into the table?

You have numbers as strings and nulls as strings .... the best way to parse it is to fix it first to use meaningful data types rather than always using strings. — MT0, Apr 28 '22 at 14:46
Does this answer your question? [How to parse json in oracle sql? (Version:11.2.0)](https://stackoverflow.com/questions/58485015/how-to-parse-json-in-oracle-sql-version11-2-0) — OldProgrammer, Apr 28 '22 at 14:54
Numbers as strings (meaning: enclosed in double-quotes) can be fixed, even though it's strictly speaking wrong. Null enclosed in double-quotes becomes a (non-null!!) string; that is absolutely a mistake, and I hope it's one you introduced in the example, and the actual JSON you receive doesn't make the same mistake. Other than that: you mention "procedure". Why **procedure**? Or perhaps you didn't actually mean it like that? There is no need for PL/SQL code for this task. — , Apr 28 '22 at 14:55
Reason is, they have thousands of records to insert into the table. So, I thought to create a procedure, let them call it passing the parameter, and I'll parse it and insert it......... — Landon Statis, Apr 28 '22 at 15:18
That makes no sense. Create a table (perhaps a global temp table if that's its only use), store the input strings - as CLOB - one per row, and then have a plain-SQL `insert` statement that parses all JSON strings in one pass. From each individual row in the temp table (one JSON document in CLOB format) the result will be one row, split between many columns, in your target table. Much cleaner and faster than processing one JSON at a time, especially if they send you thousands of JSON strings at the same time (not one at a time). — , Apr 28 '22 at 15:34

MT0 · Answer 1 · 2022-04-28T17:16:29.857

0

Use JSON_TABLE:

CREATE PROCEDURE insert_json (i_json IN CLOB)
IS
BEGIN
  INSERT INTO your_table (
    signal_id, ts_id, add_price, qty, stops, yield, close_date, close_price,
    ticker, option_ticker, signal_date, estimated_reporting_date
    /*...*/
  )
  SELECT *
  FROM   JSON_TABLE(
           i_json,
           '$'
           COLUMNS(
             signal_id     NUMBER          PATH '$.signal_id',
             ts_id         NUMBER          PATH '$.ts_id',
             add_price     NUMBER          PATH '$.add_price',
             qty           NUMBER          PATH '$.qty',
             stops         NUMBER          PATH '$.stops',
             yield         NUMBER          PATH '$.yield',
             close_date    DATE            PATH '$.close_date',
             close_price   NUMBER          PATH '$.close_price',
             ticker        VARCHAR2(10)    PATH '$.ticker',
             option_ticker VARCHAR2(10)    PATH '$.option_ticker',
             signal_date   DATE            PATH '$.signal_date',
             estimated_reporting_date DATE PATH '$.estimated_reporting_date'
             -- ...
           )
         );
END insert_json;
/

db<>fiddle here

edited Apr 28 '22 at 17:16

answered Apr 28 '22 at 14:52

MT0

143,790
11
59
117

This is good, but since they will be calling this procedure thousands of times, passing in the JSON string, can I do this, using a variable, like v_json_string CLOB, vs hard coding the actual text. – Landon Statis Apr 28 '22 at 15:21
@LandonStatis - like all other functions, `JSON_TABLE` can take a column name as its argument, it doesn't have to be a hard-coded string. If the input JSON string is in a column named `json_str` in a table `temp_tbl`, you can do exactly the same thing as MT0 has shown here; the syntax is just slightly different, to reference the table and the column in it. – Apr 28 '22 at 15:36
@LandonStatis If you want it in a procedure then just wrap it in a `CREATE PROCEDURE ...` statement and replace the hardcoded string with a variable. – MT0 Apr 28 '22 at 16:36
Hi, and thanks for all the pointers. I was looking at this link, thinking it might help: http://stevenfeuersteinonplsql.blogspot.com/2018/01/using-jsontable-to-move-json-data-to.html. – Landon Statis Apr 28 '22 at 16:38
1

@LandonStatis It is effectively the same as my answer; except they've added a useless `CROSS JOIN` to the `DUAL` table and passed in the JSON via a variable, like I said in the previous comment. – MT0 Apr 28 '22 at 16:41

Oracle Parse JSON Variable into table

1 Answers1