How to Optimize Parsing Json Array inside PostgreSQL function

Question

I wrote PostgreSQL function in which JSON array is parsed inside Loop. The Problem is it is taking too much time in case JSON Array size exceeds 1000 data. On top of that I used 2 IF statements inside loop. Is there some ways to make it faster.

My code

    WHILE incr < json_size LOOP
        jur_name2 := (SELECT my_json -> 'data' ->incr ->> 'JUR_NAME');
        filial_code2 := (SELECT my_json -> 'data' ->incr ->> 'FILIAL_CODE');
        activity_name2 := (SELECT my_json -> 'data' ->incr ->> 'ACTIVITY_NAME');
        client_code2 := (SELECT my_json -> 'data' ->incr ->> 'CLIENT_CODE');
        inn2 := (SELECT my_json -> 'data' ->incr ->> 'INN');
        account2 := (SELECT my_json -> 'data' ->incr ->> 'ACCOUNT');
        director_name2 := (SELECT my_json -> 'data' ->incr ->> 'DIRECTOR_NAME');
        director_passport2 := (SELECT my_json -> 'data' ->incr ->> 'DIRECTOR_PASSPORT');
        ----------------------------------
        phone_number2 := (SELECT my_json -> 'data' ->incr ->> 'PHONE_NUMBER');
        reg_date2 := (SELECT my_json -> 'data' ->incr ->> 'REG_DATE');
        owner_name2 := (SELECT my_json -> 'data' ->incr ->> 'OWNER_NAME');
        accountant_name2 := (SELECT my_json -> 'data' ->incr ->> 'ACCOUNTANT_NAME');
        terminal2 := (SELECT my_json -> 'data' ->incr ->> 'TERMINAL');
        region_id2 := (SELECT my_json -> 'data' ->incr ->> 'REGION_ID');
        credit_remainder := (SELECT my_json -> 'data' ->incr ->> 'CREDIT_REMAINDER');
        credit_cnt   := (SELECT my_json -> 'data' ->incr ->> 'CRED_CNT');
        end_date     := (SELECT CAST(my_json -> 'data' ->incr ->> 'END_DATE' AS DATE));

        IF(NOT EXISTS(SELECT * FROM sp_clients WHERE client_code = client_code2)) THEN

            INSERT INTO sp_clients(jur_name,filial_code,activity_name,client_code,inn,account,director_name,director_passport,phone_number,reg_date,owner_name,accountant_name,num_of_employees,plastic_cards,terminal,region_id,client_type) 
                VALUES(jur_name2,filial_code2,activity_name2,client_code2,inn2,account2,director_name2,director_passport2,phone_number2,reg_date2,owner_name2,accountant_name2,num_of_employees2,plastic_cards2,terminal2,region_id2,cl_type);
            
            new_clients_incr := new_clients_incr + 1;
            
        END IF;

        INSERT INTO loan_portfolio(client_code,summa,cnt,oper_month) 
            SELECT client_code2,credit_remainder,credit_cnt,now()::date 
            WHERE 
                NOT EXISTS (
                  SELECT 1 FROM loan_portfolio WHERE oper_month = now()::date AND client_code = client_code2
             );

        incr := incr + 1;

    END LOOP;

json

{
    "data": [
        {
            "ACTIVITY_NAME": "Gardening",
            "REGION_ID": 24,
            "FILIAL_CODE": "00760",
            "TERMINAL": 0,
            "DIRECTOR_PASSPORT": "DD12453",
            "INN": "233245",
            "REG_DATE": "2020-07-01",
            "END_DATE": "2020-06-30",
            "CRED_CNT": 6,
            "CREDIT_REMAINDER": 1.443828556508E10,
            "ACCOUNT": "24546000200199297001",
            "CLIENT_CODE": "65434234",
            "JUR_NAME": "ggg",
            "OWNER_NAME": "John Wick",
            "DIRECTOR_NAME": "Iron Man",
            "ACCOUNTANT_NAME": "Spider Man",
            "PHONE_NUMBER": "000123321"
        }
    ]
}

As you can see above code there bunch of attributes inside json array and looping through it taking much time. In addition I created index for columns that I check with If statements

Answer 1

You can do this with a single SQL statement, no loop or PL/pgSQL required

with input (data) as (
  -- your full JSON goes here
  values ('{"data": [ { ...}, {....} ]}'::jsonb)
), new_clients as (
  INSERT INTO sp_clients (jur_name,filial_code,activity_name,client_code,inn,account,director_name,director_passport,phone_number,reg_date,owner_name,accountant_name,num_of_employees,plastic_cards,terminal,region_id,client_type) 
  SELECT x.e ->> 'JUR_NAME', x.e ->> 'FILIAL_CODE', ... and so on 
  from input 
    cross join jsonb_array_elements (data -> 'data') as x(e)
  on conflict (client_code) do nothing
)
INSERT INTO loan_portfolio(client_code,summa,cnt,oper_month) 
SELECT x.e ->> 'CLIENT_CODE', x.e ->> 'CREDIT_REMAINDER', x.e ->> 'CRED_CNT', current_date
from input 
  cross join jsonb_array_elements (data -> 'data') as x(e)
on conflict (oper_month, client_code) do nothing;

If you want to put that into a function nevertheless and pass the JSON as a parameter, you can simplify this a bit by calling jsonb_array_elements() once on the parameter:

with input as (
  select x.e ->> 'CLIENT_CODE' as client_code, 
         x.e ->> 'JUR_NAME' as jur_name, 
         x.e ->> 'FILIAL_CODE' as filial_code,
         x.e ->> 'CREDIT_REMAINDER' as credit_remainder,
         x.e ->> 'CRED_CNT' as cred_cnt,
         ... repeat for all keys
  from jsonb_array_elements(p_jsonb_parameter -> 'data') as x(e)
), new_clients as (
  INSERT INTO sp_clients (jur_name,filial_code,activity_name,client_code,inn,account,director_name,director_passport,phone_number,reg_date,owner_name,accountant_name,num_of_employees,plastic_cards,terminal,region_id,client_type) 
  SELECT jur_name, filial_code, .... other columns
  from input 
  on conflict (client_code) do nothing
)
INSERT INTO loan_portfolio(client_code,summa,cnt,oper_month) 
SELECT client_code, credit_remainder, cred_cnt, current_date
from input 
on conflict (oper_month, client_code) do nothing;