Update Json column

forough m

unread,

Feb 27, 2018, 1:09:18 AM2/27/18

to

Hello,
I create a table with a Json type column. I need to update one key:value in Json. i.e increment value of 3 to 5. Can i do it?

Laurenz Albe

unread,

Feb 27, 2018, 8:04:10 AM2/27/18

to

forough m wrote:

> I create a table with a Json type column. I need to update one key:value
> in Json. i.e increment value of 3 to 5. Can i do it?

If you have PostgreSQL 9.5 or better, you can use "json_set" like this:

CREATE TABLE jsontest (
id integer PRIMARY KEY,
j jsonb
);

INSERT INTO jsontest VALUES (1, '{"a": 12, "b": {"c": 12, "d": 4}}');

TABLE jsontest;

id | j
----+-----------------------------------
1 | {"a": 12, "b": {"c": 12, "d": 4}}
(1 row)

UPDATE jsontest
SET j = jsonb_set(
j,
'{b,c}',
((j #>> '{b,c}')::integer + 30)::text::jsonb
)
WHERE id = 1;

TABLE jsontest;

id | j
----+-----------------------------------
1 | {"a": 12, "b": {"c": 42, "d": 4}}
(1 row)

forough m

unread,

Feb 28, 2018, 6:06:29 AM2/28/18

to

Than you soo much :)

forough m

unread,

Feb 28, 2018, 6:21:31 AM2/28/18

to

On Tuesday, February 27, 2018 at 4:34:10 PM UTC+3:30, Laurenz Albe wrote:

Sorry. I test it but it has no result. SQL query executed without any error but value in Json not changed.

Laurenz Albe

unread,

Mar 1, 2018, 5:18:05 AM3/1/18

to

forough m wrote:

>>> I create a table with a Json type column. I need to update one
>>> key:value in Json. i.e increment value of 3 to 5. Can i do it?
>>
>> If you have PostgreSQL 9.5 or better, you can use "json_set" like this:
>>

>> [...]

>>
> Sorry. I test it but it has no result. SQL query executed without any
> error but value in Json not changed.

Then you must be doing something wrong.
Hard to say what if you don't show your query.

forough m

unread,

Mar 5, 2018, 4:44:54 AM3/5/18

to

Worked. Thanks :) But, why it remove all fields if {b,c} not exist?
I want to insert it if not exist

forough m

unread,

Mar 6, 2018, 10:47:04 AM3/6/18

to

I want to say: If 'f' exist, then increment it +3 else, if not exist create it with value = 0

Laurenz Albe

unread,

Mar 7, 2018, 2:48:32 AM3/7/18

to

forough m wrote:
>> > >>> I create a table with a Json type column. I need to update one
>> > >>> key:value in Json. i.e increment value of 3 to 5. Can i do it?
>> > >>
>> > >> If you have PostgreSQL 9.5 or better, you can use "json_set" like
>> > >> this:
>>

>> Worked. Thanks :) But, why it remove all fields if {b,c} not exist?
>> I want to insert it if not exist
>
> I want to say: If 'f' exist, then increment it +3 else, if not exist
> create it with value = 0

You could use CASE:

UPDATE t SET j = CASE WHEN <j contains key>
THEN <j with 3 added>
ELSE <j with new key>
END

forough m

unread,

Mar 7, 2018, 3:09:06 AM3/7/18

to

Wow, very good. thank you. How should i check <j contains key> ?

forough m

unread,

Mar 7, 2018, 3:10:56 AM3/7/18

to

I try it:
UPDATE data_huawei SET counters = CASE WHEN (counters -> '{a}')
THEN jsonb_set(  counters,  '{a}',  ((counters #>> '{a}')::integer + 30)::text::jsonb  )
ELSE jsonb_set(  counters,  '{a}', "10")
END
But it's not correct syntax

Laurenz Albe

unread,

Mar 8, 2018, 6:36:23 AM3/8/18

to

On Wed, 07 Mar 2018 00:09:05 -0800, forough m wrote:
>> You could use CASE:
>>
>> UPDATE t SET j = CASE WHEN <j contains key>
>> THEN <j with 3 added>
>> ELSE <j with new key>
>> END
>
> Wow, very good. thank you. How should i check <j contains key> ?

Hm. Why don't you look at the documentation, section
"JSON functions and operators":

https://www.postgresql.org/docs/current/static/functions-
json.html#FUNCTIONS-JSONB-OP-TABLE

@> is right there.

forough m

unread,

Mar 10, 2018, 12:07:52 AM3/10/18

to

Sure, thanks :)