How to do an insert to a table with a JSON?

Edisson Gabriel López

Hi,

I need a PL/SQL that makes insert with a JSON, how can I do it?

The JSON I have is the following:

[
  {
    "id": 38,
    "names": "Gerald Smith",
    "selected": true
  },
  {
    "id": 28,
    "names": "Camilo Reyes",
    "selected": true
  },
  {
    "id": 24,
    "names": "Edisson Lopez",
    "selected": true
  },
  {
    "id": 36,
    "names": "Gerald Pineda",
    "selected": false
  },
  {
    "id": 37,
    "names": "Rodrigo Vargas",
    "selected": false
  }
]

I need to store the three fields in a table, thank you very much.

Paulzip

You don't need PL/SQL, it can be done in SQL. Your source table won't support a boolean type, so you'll need to map "selected" to something else, e.g. 0 and 1.

create table my_table (
  id       number primary key
, names    varchar2(100)
, selected number(1)  
);
  
insert into my_table(id, names, selected)  
select id, names
     , case selected when 'true' then 1 when 'false' then 0 end selected  
from   json_table(
'[
{
"id": 38,
"names": "Gerald Smith",
"selected": true
},
{
"id": 28,
"names": "Camilo Reyes",
"selected": true
},
{
"id": 24,
"names": "Edisson Lopez",
"selected": true
},
{
"id": 36,
"names": "Gerald Pineda",
"selected": false
},
{
"id": 37,
"names": "Rodrigo Vargas",
"selected": false
}
]'
       , '$[*]'
         columns (
           id       number   path '$.id'
         , names    varchar2 path '$.names'
         , selected varchar2 path '$.selected'
         )
       );
           
select *
from   my_table;

5 rows created.


        ID NAMES                  SELECTED
---------- -------------------- ----------
        38 Gerald Smith                  1
        28 Camilo Reyes                  1
        24 Edisson Lopez                 1
        36 Gerald Pineda                 0
        37 Rodrigo Vargas                0

Paulzip

You don't need PL/SQL, it can be done in SQL. Your source table won't support a boolean type, so you'll need to map "selected" to something else, e.g. 0 and 1.

create table my_table (
  id       number primary key
, names    varchar2(100)
, selected number(1)  
);
  
insert into my_table(id, names, selected)  
select id, names
     , case selected when 'true' then 1 when 'false' then 0 end selected  
from   json_table(
'[
{
"id": 38,
"names": "Gerald Smith",
"selected": true
},
{
"id": 28,
"names": "Camilo Reyes",
"selected": true
},
{
"id": 24,
"names": "Edisson Lopez",
"selected": true
},
{
"id": 36,
"names": "Gerald Pineda",
"selected": false
},
{
"id": 37,
"names": "Rodrigo Vargas",
"selected": false
}
]'
       , '$[*]'
         columns (
           id       number   path '$.id'
         , names    varchar2 path '$.names'
         , selected varchar2 path '$.selected'
         )
       );
           
select *
from   my_table;

5 rows created.


        ID NAMES                  SELECTED
---------- -------------------- ----------
        38 Gerald Smith                  1
        28 Camilo Reyes                  1
        24 Edisson Lopez                 1
        36 Gerald Pineda                 0
        37 Rodrigo Vargas                0