How to loop JSON array in postgres and access keys of the JSON?

Question

I have a custom json with several key-value pairs. I want to loop over the length of the array, and access each of their keys, and insert into the table. The problem I am facing is during loop the query is unable to access the value.

do $$
DECLARE
DECLARE fname varchar;
DECLARE lname varchar;
DECLARE StartDate timestamp;
DECLARE EndDate timestamp;
DECLARE DateText varchar;
DECLARE uniqueData jsonb = '[
    {
        "fname": "PERFFBB",
        "lname": "PERFE59-AB1899A-A8CAE59-AB1899A-AB1899A-AB1899A-AB1899A-A8CAE59"
    },
    {
        "fname": "PERFD34",
        "lname": "PERFCD5-232D322-BD88CD5-232D322-232D322-232D322-232D322-BD88CD5"
    },
]';
begin for r in 1..(jsonb_array_length(uniqueData) - 1)
loop 
fname = uniqueData[r].fname;
lname = uniqueData[r].lname;
-- this is giving a problem. It is unable to access the key.
for z in 1..2 
loop 
StartDate = current_date::timestamp - concat(z, ' day')::interval;
EndDate = current_date::timestamp - concat(z - 1, ' day')::interval - interval '1 seconds';
DateText = (
    to_char(
        'now'::timestamp - concat(z, ' day')::interval,
        'Mon DD, YYYY'
    )
);
INSERT INTO table (
        col1,
        col2,
        col3,
        col4,
        col5
    )
values (
        fname,
        lname,
        StartDate,
        EndDate,
        DateText
    );
end loop;
end loop;
end;
$$;

Игорь Тыра · Accepted Answer · 2020-10-07 17:36:37Z

1

the loop over jsonb array will look like this

DECLARE var1 RECORD;
...
FOR var1 IN
  SELECT * FROM jsonb_to_recordset(uniqueData) as x(fname text, lname text)
LOOP
  -- var1 contains fname and lname fields
  fname = var1.fname;
  lname = var1.lname;

  .....

END LOOP;

answered Oct 7, 2020 at 17:36

Игорь Тыра

9755 silver badges12 bronze badges

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

2 Comments

Aijaz Over a year ago

Hi, Thanks. I am getting this error. ERROR: invalid input syntax for type json Detail: Expected JSON value, but found "]".

Игорь Тыра Over a year ago

extra coma in declaration breaks the JSON, your JSON is [{...},{...},] and is not valid due to last comma

Collectives™ on Stack Overflow

How to loop JSON array in postgres and access keys of the JSON?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related