Parse Json and insert into oracle table

Question

I have a json variable and I want to insert all data to my table.

The json response is like this:

    {
    "data": [{
        "pid": "10",
        "name": "sss",
        "consumer_price": "100",
        "discount": "10",
        "sale_price": "90"
    }, {
        "pid": "11",
        "name": "fff",
        "consumer_price": "100",
        "discount": "10",
        "sale_price": "90"
    }]
 }

I try using JSON_TABLE to insert the values into my table

INSERT INTO tbl_product_temp (pid, name, consumer_price,discount,sale_price)

    SELECT key1, key2 , key3,key4,key5
      FROM JSON_TABLE (' 

       {
    "data": [{
        "pid": "10",
        "name": "sss",
        "consumer_price": "100",
        "discount": "10",
        "sale_price": "90"
    }, {
        "pid": "11",
        "name": "fff",
        "consumer_price": "100",
        "discount": "10",
        "sale_price": "90"
    }]

 }
',
                       '$.data'
                       COLUMNS 
                       key1 VARCHAR2 PATH '$.pid',
                       key2 VARCHAR2 PATH '$.name',
                       key3 VARCHAR2 PATH '$.consumer_price',
                       key4 VARCHAR2 PATH '$.discount',
                       key5 VARCHAR2 PATH '$.sale_price'
                       );

but it does not work. actually it works for one set of data but for more it still does not work please help me.

"but it does not work" tells exactly nothing to us. What the error you get? — astentx, Aug 04 '21 at 16:10
@astentx: hi, it does not have any error. the query return nothings. I made a little mistake and someone find the problem, thank you anyway — navid sedigh, Aug 04 '21 at 16:15

score 3 · Accepted Answer · answered Aug 04 '21 at 16:06

$.data selects the array, but the array doesn't have a .pid key, for example. $.data[*] selects all the items in the array, which is what you want.

SELECT key1, key2 , key3,key4,key5
      FROM JSON_TABLE (' 

       {
    "data": [{
        "pid": "10",
        "name": "sss",
        "consumer_price": "100",
        "discount": "10",
        "sale_price": "90"
    }, {
        "pid": "11",
        "name": "fff",
        "consumer_price": "100",
        "discount": "10",
        "sale_price": "90"
    }]

 }
',
                       '$.data[*]'
                       COLUMNS 
                       key1 VARCHAR2 PATH '$.pid',
                       key2 VARCHAR2 PATH '$.name',
                       key3 VARCHAR2 PATH '$.consumer_price',
                       key4 VARCHAR2 PATH '$.discount',
                       key5 VARCHAR2 PATH '$.sale_price'
                       );

Thanks You save me. I merge your solution with this:`https://stackoverflow.com/a/64717873/12780274` And work for me. — henrry, Jul 23 '22 at 18:46
this tutorial is for big json in python `https://stackoverflow.com/a/73168799/12780274` — henrry, Jul 30 '22 at 10:46

Parse Json and insert into oracle table

1 Answers1

Linked