Thread: [SQL] Updating jsonb rows
this ...
DO
$BODY$
declare
j_final_rslt jsonb;
begin
select jsonb_agg(row_to_json(alias)) from
(select prompt_seq, attribute_name from tfinal_rslt limit 2) alias into j_final_rslt;
raise notice 'j_final_rslt BEFORE %',j_final_rslt;
select jsonb_set(j_final_rslt, '{0,prompt_seq}','99',true) into j_final_rslt;
raise notice 'j_final_rslt AFTER %',j_final_rslt;
end;
$BODY$
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:
AFTER [{"prompt_seq": 99, "attribute_name": "InsuredTimeframe"}, {"prompt_seq": 1200, "attribute_name": "ABC"}]
tia, Mike
select 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.
David J.
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'
On Fri, Aug 11, 2017 at 6:18 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
select 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.David J.
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.
I don't presently know a better solution in PostgreSQL.
Dave
Probably the solution is "don't try to use JSON as temp tables for anything but very small datasets". As you know, I'm converting an application that uses temp tables to NOT use temp tables because it needs to run in a read-only database. JSONB seemed to be the way to go because you can almost treat them the same as temp tables. I think in the end, it will be "fast enough", but just not as fast as temp tables. Thanks for your help!
Mike
On Mon, Aug 14, 2017 at 11:12 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
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.I don't presently know a better solution in PostgreSQL.Dave
Here are some final result timings on Temp Tables vs JSONB. _25_sec means .25 seconds. This is the result of ~2,300 executions of my function using various input parameters.
Basically what it shows is that even though the average response time for Temp Tables is superior, JSONB wins in consistency. The Temp Tables approach has some horrible outliers.
The max response time for Temp Tables was over 10.8 seconds, while the max for JSONB was 1.2 seconds.
Obviously, nobody should make any generalizations from my very specific results, but if you need to get off of Temp Tables, going to JSONB is worth considering.
Mike
On Mon, Aug 14, 2017 at 12:33 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
Probably the solution is "don't try to use JSON as temp tables for anything but very small datasets". As you know, I'm converting an application that uses temp tables to NOT use temp tables because it needs to run in a read-only database. JSONB seemed to be the way to go because you can almost treat them the same as temp tables. I think in the end, it will be "fast enough", but just not as fast as temp tables. Thanks for your help!MikeOn Mon, Aug 14, 2017 at 11:12 AM, David G. Johnston <david.g.johnston@gmail.com> wrote: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.I don't presently know a better solution in PostgreSQL.Dave