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

One way to optimize is to remove the useless SELECTs: inn2 := (SELECT my_json -> 'data' ->incr ->> 'INN'); can be simplified to inn2 := my_json -> 'data' ->incr ->> 'INN'; — user330315
– user330315, Commented Jul 14, 2020 at 6:11
Please edit your question and add the JSON input as formatted text. This most certainly can be simplified to a simple insert ... select... using e.g. jsonb_each() or jsonb_array_elements() in the SELECT statement. — user330315
– user330315, Commented Jul 14, 2020 at 6:12
Are there unique indexes (or constraints) on sp_clients.client_code and loan_portfolio(oper_month, client_code)? — user330315
– user330315, Commented Jul 14, 2020 at 6:29
sp_clients.client_code is unique. But loan_portfolio(oper_month, client_code) can be repeated as oper_month may vary for particular client_code — Abdusoli
– Abdusoli, Commented Jul 14, 2020 at 6:33

score 1 · Accepted Answer · 2020-07-14 06:52:27Z

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;

edited Jul 14, 2020 at 6:52

answered Jul 14, 2020 at 6:43

user330315

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

5 Comments

Abdusoli Over a year ago

I will check your answer now

Abdusoli Over a year ago

Your answer is working really good. But I had to change jsonb_array_elements to json_array_elements. I dont know the reason why jsonb is giving ERROR: function jsonb_array_elements(json) does not exist

user330315 Over a year ago

Using jsonb over json is preferred though.

Abdusoli Over a year ago

Does it depend on Postgre Version to make use of jsonb or Do I need to create extension. PostgreSQL version is : 9.6.13

user330315 Over a year ago

jsonb was introduced in Postgres 9.4, so you can use it in Postgres 9.6 without problems. Quote from the manual "In general, most applications should prefer to store JSON data as jsonb"

Collectives™ on Stack Overflow

How to Optimize Parsing Json Array inside PostgreSQL function

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related