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
– astentx, Commented Aug 4, 2021 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
– navid sedigh, Commented Aug 4, 2021 at 16:15

kfinity · Accepted Answer · 2021-08-04 16:06:27Z

3

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

answered Aug 4, 2021 at 16:06

kfinity

9,1411 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

henrry Over a year ago

Thanks You save me. I merge your solution with this:https://stackoverflow.com/a/64717873/12780274 And work for me.

henrry Over a year ago

this tutorial is for big json in python https://stackoverflow.com/a/73168799/12780274

Collectives™ on Stack Overflow

Parse Json and insert into oracle table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related