Hi David, finally got a chance to look at this. I don't see how the SO example applies to my case. Basically what I am doing is trying to simulate a SQL UPDATE statement. currently I am doing this, and it works, but it is slow, so I am looking for a better way.
[In reality there are about 40 columns and up to 600 rows ]
select jsonb_agg(row_to_json(alias)) from
(select
question_seq,
case select_type when 'EDUPROGRAM' then -10 else prompt_seq end as prompt_seq,
jsonb_populate_recordset(null::ypxportal2__fgetquestions,j_final_rslt) rt)alias into j_final_rslt;
The SQL equiv of this would be:
Update mytable, set prompt_seq = -10 where select_type = 'EDUPROGRAM'
elect jsonb_set(j_final_rslt, '{0,prompt_seq}','99',true) into j_final_rslt;
gives the result:
BEFORE [{"prompt_seq": 150, "attribute_name": "InsuredTimeframe"}, {"prompt_seq": 1200, "attribute_name": "DriverOccupation"}]
AFTER [{"prompt_seq": 99, "attribute_name": "InsuredTimeframe"}, {"prompt_seq": 1200, "attribute_name": "DriverOccupation"}]
How could I change this to ALSO change the attribute_name on the 2nd record to "ABC". The AFTER result would ideally look like:
This SO post seems like it should get you close. Basically you pull out the 0th element, concatenate in the values you want to change, and supply that result as the third jsonb_set argument.