How to load data from data from nested json to postgresql?

Question

I Am trying to load data from Nested JSON to a PostgreSQL DB.

This is a sample data from the json:

{
  "ID":"4654554",
  "Skill_Set" : [ "RN", "React.js", "Node.js", "JS", "D3", "Oracle", "Jenkins", "Spring", "GoogleCloud", "Maven" ],
  "Modified_By" : {
      "name" : "Admin",
      "id" : "545512121"
    },
  "approval" : {
      "first" : false,
      "second" : false,
      "third" : false,
      "all" : false
      }
 }

The Expected Result is a table with the structure below:

ID | Skill_SET | Modified_By_name | Modified_By_id | approvel_first | approvel_second
4654554 | "RN", "React.js", "Node.js", "JS", "D3", "Oracle", "Jenkins", "Spring", "GoogleCloud", "Maven" | Admin | 545512121 |  false ...

• How can I define columns data types?

• How to load this file into the database?

• PS: * I don't need a command to load the data into the DB, I need to Understand the method to convert the "array" and the record to a database data types.

Answer 1

reference: json_array to postgresql array

create table test_insert_jsonb(id bigint,
            skill_set text[], 
            modified_by jsonb, 
            approval jsonb);

then write a function input is text, output is the insert result.
load the file as text string. demo

create or replace  function jsonb_insert(p_object text)
returns setof text AS
$body$
    declare
    p_jsonb jsonb; v_rec record;
    v_id bigint;v_Modified_By jsonb;
    v_approval jsonb;v_skill_set text[];
    begin
    p_jsonb := p_object::jsonb;
    if (p_jsonb['ID'] ) is null then raise exception 'no id this function will not work.';
    
    else
        for v_rec in (select * from jsonb_each(p_jsonb))
        loop
            case
            when v_rec.key = 'ID' then v_id := trim( both '"' from (v_rec.value::text));
            when v_rec.key = 'Skill_Set' then
                v_skill_set := jsonb_array_to_text_array(v_rec.value);
            when v_rec.key = 'Modified_By' then v_Modified_By := v_rec.value;
            when v_rec.key = 'approval' then v_approval := v_rec.value;
            else null;
            end case;
        end loop;
        insert into test_insert_jsonb(id, skill_set,modified_by,approval)
                values(v_id,v_skill_set,v_Modified_By, v_approval)
                    returning id into v_id;
        return query
                select row_to_json(e.*)::text from test_insert_jsonb e where id = v_id;
    end if;
    end
$body$
language plpgsql;