How to insert(bulk) Handsontable Json array into a oracle table?

Question

var hot = new Handsontable(container, {}
JSON.stringify({data: hot.getData()})

JSON: { data:  [
      "col_1" // These are the table columns 
      "col_2"     
    ],
    [
      "test2",// there are values
      "201702"      
    ],
    [
      "test2",// there are values
      "201702"
    ]
}

Here columns are always dynamic.i need to built insert query accordingly.could some help me how can i insert handsontable data into a table.

If the columns are "always dynamic" then potentially you'll need a different table every time. Is that what you have in mind? What are you going to do with this data once you've loaded it? Why not just store the JSON doc as a CLOB? — APC, May 02 '17 at 13:52
based on column names i need to build insert query to a table.every time i will insert into same table but if user select only one column load is less right. my query is like this insert into a table(col_1,col2) values("test2","201702") and so on.. — Shiv, May 02 '17 at 16:11
So your table is a generic one? A single table for all spreadsheets? How will you know which rows belong together? How do you store spreadsheet column names? — APC, May 02 '17 at 16:15
Is this not an solution: http://stackoverflow.com/questions/42702150/oracle-json-to-key-value-pair-table — mark d drake, May 02 '17 at 20:43
I solved this problem using JSON_TABLE concept.will post solution soon.thanks everyone — Shiv, May 03 '17 at 19:50

score 1 · Answer 1 · answered Jan 13 '20 at 08:32

This worked for me in 12.1.0.2.0

SELECT * 
FROM   JSON_TABLE (' [["USD" , "GBP", 1.2],
                  ["USD" , "EUR", 1.1],
                  ["GBP" , "EUR",  .9]]', '$[*]'  columns ( 
                                          c varchar2(100) path '$[0]',
                                          tc varchar2(100) path '$[1]',
                                          cr number path '$[2]'));

score 0 · Answer 2 · answered May 03 '17 at 14:45

You JSON is not valid for starters..

However if I assume that you meant to have an array of arrays, then something like this should work for you

SQL> create table SOURCE_DOCUMENTS (
  2    ID NUMBER,
  3    JSON_DOC CLOB CHECK (JSON_DOC IS JSON)
  4  )
  5  /

Table created.

SQL> insert into SOURCE_DOCUMENTS values (1,'{
  2  "data": [
  3  [
  4  "col_1", "col_2"
  5  ], [
  6  "test2", "201702"
  7  ], [
  8  "test2", "201702"
  9  ]
 10  ]
 11  }')
 12  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> create or replace procedure insertFromJSON(P_ID NUMBER)
  2  AUTHID CURRENT_USER
  3  IS
  4    JSON_DOC         CLOB;
  5    JO               JSON_OBJECT_T;
  6    DATA             JSON_ARRAY_T;
  7    COLUMN_DETAILS   JSON_ARRAY_T;
  8    I                PLS_INTEGER := 0;
  9    COLUMN_LIST      VARCHAR2(32767) := '';
 10    COLUMN_PATTERN   VARCHAR2(32767) := '';
 11    STATEMENT        VARCHAR2(32767) := '';
 12  begin
 13
 14    SELECT JSON_DOC
 15      into JSON_DOC
 16      from SOURCE_DOCUMENTS
 17     where ID = P_ID;
 18
 19    JO   := JSON_OBJECT_T(JSON_DOC);
 20    DATA := TREAT(JO.get('data') as JSON_ARRAY_T);
 21    COLUMN_DETAILS := TREAT(DATA.get(0) as JSON_ARRAY_T);
 22    DBMS_OUTPUT.put_line('Column Count = ' || COLUMN_DETAILS.get_size());
 23    WHILE (i < COLUMN_DETAILS.get_size()) LOOP
 24      COLUMN_LIST := COLUMN_LIST || ',' || '"' || COLUMN_DETAILS.get_string(i) || '"';
 25      COLUMN_PATTERN := COLUMN_PATTERN || ',' || '"' || COLUMN_DETAILS.get_string(i) || '"' || ' VARCHAR2(32) PATH ''
$[' || i || ']''';
 26      i := i + 1;
 27    end loop;
 28    COLUMN_LIST := SUBSTR(COLUMN_LIST,2);
 29    COLUMN_PATTERN := SUBSTR(COLUMN_PATTERN,2);
 30    -- COLUMN_PATTERN := 'IDX FOR ORDINALITY ' || COLUMN_PATTERN;
 31    STATEMENT := 'CREATE TABLE TEST as select ' || COLUMN_LIST || ' from ( select ROWNUM ROW_NUM, ' || COLUMN_LIST ||
 ' FROM SOURCE_DOCUMENTS, JSON_TABLE(JSON_DOC,''$.data[*]'' columns ' || COLUMN_PATTERN || ') where ID = ' || P_ID || ')
 WHERE ROW_NUM > 1';
 32    DBMS_OUTPUT.put_line('Statement = ' || STATEMENT);
 33    EXECUTE IMMEDIATE STATEMENT;
 34  end;
 35  /

Procedure created.

SQL> show errors
No errors.
SQL> --
SQL> set serveroutput on
SQL> /

Procedure created.

SQL> call insertFromJSON(1)
  2  /
Column Count = 2
Statement = CREATE TABLE TEST as select "col_1","col_2" from ( select ROWNUM
ROW_NUM, "col_1","col_2" FROM SOURCE_DOCUMENTS, JSON_TABLE(JSON_DOC,'$.data[*]'
columns "col_1" VARCHAR2(32) PATH '$[0]',"col_2" VARCHAR2(32) PATH '$[1]') where
ID = 1) WHERE ROW_NUM > 1

Call completed.

SQL> commit
  2  /

Commit complete.

SQL> select *
  2    from TEST
  3  /

col_1                            col_2
-------------------------------- --------------------------------
test2                            201702
test2                            201702

SQL>

thanks for details explaining in detail.i am trying to run your proc but giving an compilation error.may i know why so Error: PLS-00201: identifier 'JSON_OBJECT_T' must be declared Line: 5 Text: JO JSON_OBJECT_T; Error: PL/SQL: Item ignored Line: 5 Text: JO JSON_OBJECT_T; Error: PLS-00201: identifier 'JSON_ARRAY_T' must be declared Line: 6 Text: DATA JSON_ARRAY_T; — Shiv, May 04 '17 at 20:03
Sorry, should have stated this solution requires Oracle 12.2.0.1.0. The PL/SQL JSON support is new with this release. — mark d drake, May 05 '17 at 04:21

mark d drake · Answer 3 · 2017-05-05T05:06:29.457

Here's an alternative that should work in 12.1.0.2.0

SQL> create or replace procedure insertFromJSON(P_ID NUMBER)
   2   AUTHID CURRENT_USER
   3   IS
   8     I                PLS_INTEGER := 0;
   9     COLUMN_LIST      VARCHAR2(32767) := '';
  10     COLUMN_PATTERN   VARCHAR2(32767) := '';
  11     STATEMENT        VARCHAR2(32767) := '';
  12
  13     CURSOR COL_CURSOR
  14     is
  15     select COLUMN_NAME
  16       from SOURCE_DOCUMENTS,
  17            JSON_TABLE(
  18              JSON_DOC,
  19              '$.data[0][*]'
  20              columns
  21                COLUMN_NAME VARCHAR2(32) path '$'
  22            );
  23
  24   begin
  25
  26    for C in COL_CURSOR LOOP
  27      COLUMN_LIST := COLUMN_LIST || ',' || '"' || c.COLUMN_NAME || '"';
  28      COLUMN_PATTERN := COLUMN_PATTERN || ',' || '"' || c.COLUMN_NAME || '"' || ' VARCHAR2(32) PATH '' $[' || i || '
 ''';
  29      i := i + 1;
  30    end loop;
  31
  32    COLUMN_LIST := SUBSTR(COLUMN_LIST,2);
  33    COLUMN_PATTERN := SUBSTR(COLUMN_PATTERN,2);
  34    STATEMENT := 'CREATE TABLE TEST as select ' || COLUMN_LIST || ' from ( select ROWNUM ROW_NUM, ' || COLUMN_LIST |
  ' FROM SOURCE_DOCUMENTS, JSON_TABLE(JSON_DOC,''$.data[*]'' columns ' || COLUMN_PATTERN || ') where ID = ' || P_ID || '
  WHERE ROW_NUM > 1';
  35    DBMS_OUTPUT.put_line('Statement = ' || STATEMENT);
  36    EXECUTE IMMEDIATE STATEMENT;
  37   end;
  38  /

 Procedure created.

 SQL> drop table TEST
   2  /

 Table dropped.

 SQL> call insertFromJSON(1)
   2  /

 Call completed.

 SQL> commit
   2  /

 Commit complete.

 SQL> select *
   2    from TEST
   3  /

 col_1                            col_2
 -------------------------------- --------------------------------
 test2                            201702
 test2                            201702

How to insert(bulk) Handsontable Json array into a oracle table?

3 Answers3