Forum Stats

  • 3,827,745 Users
  • 2,260,818 Discussions
  • 7,897,364 Comments

Discussions

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

Edisson Gabriel López
Edisson Gabriel López Member Posts: 65 Green Ribbon

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.

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,694 Blue Diamond

    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
    

Answers

  • Paulzip
    Paulzip Member Posts: 8,694 Blue Diamond

    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