Postgres Pro
Downloads
Home   >   mailing lists

Thread: update field in jsonb

update field in jsonb

From
support-tiger
Date:
is there a way to update a single field in jsonb without replacing the 
entire json document - couldn't find an example

for example

create table test (id primary key, data jsonb);

insert into test ({"name":"bill", "age":29});
 ?? update test   set data->age = 30


-- 
Support Dept
Tiger Nassau, Inc.
www.tigernassau.com
406-624-9310

Re: update field in jsonb

From
"Ivan E. Panchenko"
Date:
23.11.2017 04:45, support-tiger пишет:
> is there a way to update a single field in jsonb without replacing the 
> entire json document - couldn't find an example
>
> for example
>
> create table test (id primary key, data jsonb);
>
> insert into test ({"name":"bill", "age":29});
>
>  ?? update test   set data->age = 30
>
>
When a record in PostgeSQL is UPDATEd, its new version is created. So 
such partial JSON update would be not more than some syntax sugar. That 
is why it is not yet implemented, though plans for that exist.

Now you have to do something like:

UPDATE test SET data = jsonb_set(data, ARRAY['age'], to_jsonb(30)) WHERE ..

Re: update field in jsonb

From
Oleg Bartunov
Date:
On Thu, Nov 23, 2017 at 4:45 AM, support-tiger <support@tigernassau.com> wrote:
> is there a way to update a single field in jsonb without replacing the
> entire json document - couldn't find an example
>
> for example
>
> create table test (id primary key, data jsonb);
>
> insert into test ({"name":"bill", "age":29});
>
>  ?? update test   set data->age = 30


update test set data = jsonb_set(data, '{age}', '30'::jsonb);

>
>
> --
> Support Dept
> Tiger Nassau, Inc.
> www.tigernassau.com
> 406-624-9310
>
>
>

Re: update field in jsonb

From
support-tiger
Date:
Oleg,

hey, thanks so much - if you are in USA visiting Yellowstone Natl Park 
contact me and the beer is on me (maybe a Wyoming steak too!)


On 11/22/2017 11:27 PM, Oleg Bartunov wrote:
> On Thu, Nov 23, 2017 at 4:45 AM, support-tiger <support@tigernassau.com> wrote:
>> is there a way to update a single field in jsonb without replacing the
>> entire json document - couldn't find an example
>>
>> for example
>>
>> create table test (id primary key, data jsonb);
>>
>> insert into test ({"name":"bill", "age":29});
>>
>>   ?? update test   set data->age = 30
>
> update test set data = jsonb_set(data, '{age}', '30'::jsonb);
>
>>
>> --
>> Support Dept
>> Tiger Nassau, Inc.
>> www.tigernassau.com
>> 406-624-9310
>>
>>
>>
>

-- 
Support Dept
Tiger Nassau, Inc.
www.tigernassau.com
406-624-9310

Re: update field in jsonb

From
Oleg Bartunov
Date:
On Fri, Nov 24, 2017 at 3:46 AM, support-tiger <support@tigernassau.com> wrote:
> Oleg,
>
> hey, thanks so much - if you are in USA visiting Yellowstone Natl Park
> contact me and the beer is on me (maybe a Wyoming steak too!)

Seriously ?

btw, in PG 11 we expect subscription index    update test set data[age]= '30';



>
>
>
> On 11/22/2017 11:27 PM, Oleg Bartunov wrote:
>>
>> On Thu, Nov 23, 2017 at 4:45 AM, support-tiger <support@tigernassau.com>
>> wrote:
>>>
>>> is there a way to update a single field in jsonb without replacing the
>>> entire json document - couldn't find an example
>>>
>>> for example
>>>
>>> create table test (id primary key, data jsonb);
>>>
>>> insert into test ({"name":"bill", "age":29});
>>>
>>>   ?? update test   set data->age = 30
>>
>>
>> update test set data = jsonb_set(data, '{age}', '30'::jsonb);
>>
>>>
>>> --
>>> Support Dept
>>> Tiger Nassau, Inc.
>>> www.tigernassau.com
>>> 406-624-9310
>>>
>>>
>>>
>>
>
> --
> Support Dept
> Tiger Nassau, Inc.
> www.tigernassau.com
> 406-624-9310
>
>
>

Re: update field in jsonb

From
"Sven R. Kunze"
Date:
On 24.11.2017 12:19, Oleg Bartunov wrote:
> btw, in PG 11 we expect subscription index
>       update test set data[age]= '30';

Out of curiosity:

Why not?

update test set data->age= 30;

It reminds me more of how to query json in PG

select data->age from test;


Cheer,
Sven