Обсуждение: [SQL] Updating jsonb rows

Поиск
Список
Период
Сортировка

[SQL] Updating jsonb rows

От
Michael Moore
Дата:
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

Re: [SQL] Updating jsonb rows

От
"David G. Johnston"
Дата:
On Fri, Aug 11, 2017 at 6:08 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
​s​
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.

https://stackoverflow.com/questions/38883233/postgres-jsonb-set-multiple-keys-update

David J.

Re: [SQL] Updating jsonb rows

От
Michael Moore
Дата:
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:
On Fri, Aug 11, 2017 at 6:08 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
​s​
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.

https://stackoverflow.com/questions/38883233/postgres-jsonb-set-multiple-keys-update

David J.


Re: [SQL] Updating jsonb rows

От
"David G. Johnston"
Дата:
On Mon, Aug 14, 2017 at 10:35 AM, Michael Moore <michaeljmoore@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
​

Re: [SQL] Updating jsonb rows

От
Michael Moore
Дата:
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:
On Mon, Aug 14, 2017 at 10:35 AM, Michael Moore <michaeljmoore@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
​

Re: [SQL] Updating jsonb rows

От
Michael Moore
Дата:
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.
Inline image 1

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!
Mike


On Mon, Aug 14, 2017 at 11:12 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Aug 14, 2017 at 10:35 AM, Michael Moore <michaeljmoore@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
​