CODEWITHSUNDEEP
jsonoracle-databaseplsqloracle12c
navid sedigh
navid sedigh

Reputation: 281

Parse Json and insert into oracle table

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.

Upvotes: 1

Views: 2702

Answers (1)

kfinity
kfinity

Reputation: 9091

$.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'
                       );

Upvotes: 3

Related Questions