Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #807 > unrolled thread

Update Json column

Started byforough m <fmirkarimzade@gmail.com>
First post2018-02-26 22:09 -0800
Last post2018-03-07 00:10 -0800
Articles 12 — 2 participants

Back to article view | Back to comp.databases.postgresql

Contents

  Update Json column forough m <fmirkarimzade@gmail.com> - 2018-02-26 22:09 -0800
    Re: Update Json column Laurenz Albe <laurenz@nospam.pn> - 2018-02-27 13:04 +0000
      Re: Update Json column forough m <fmirkarimzade@gmail.com> - 2018-02-28 03:06 -0800
      Re: Update Json column forough m <fmirkarimzade@gmail.com> - 2018-02-28 03:21 -0800
        Re: Update Json column Laurenz Albe <laurenz@nospam.pn> - 2018-03-01 10:18 +0000
          Re: Update Json column forough m <fmirkarimzade@gmail.com> - 2018-03-05 01:44 -0800
            Re: Update Json column forough m <fmirkarimzade@gmail.com> - 2018-03-06 07:47 -0800
              Re: Update Json column Laurenz Albe <laurenz@nospam.pn> - 2018-03-07 07:48 +0000
                Re: Update Json column forough m <fmirkarimzade@gmail.com> - 2018-03-07 00:09 -0800
                  Re: Update Json column Laurenz Albe <laurenz@nospam.pn> - 2018-03-08 11:36 +0000
                    Re: Update Json column forough m <fmirkarimzade@gmail.com> - 2018-03-09 21:07 -0800
                Re: Update Json column forough m <fmirkarimzade@gmail.com> - 2018-03-07 00:10 -0800

#807 — Update Json column

Fromforough m <fmirkarimzade@gmail.com>
Date2018-02-26 22:09 -0800
SubjectUpdate Json column
Message-ID<fd35c1ca-2d97-476e-88bd-36c0dd722b41@googlegroups.com>
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?

[toc] | [next] | [standalone]

#808

FromLaurenz Albe <laurenz@nospam.pn>
Date2018-02-27 13:04 +0000
Message-ID<p73l09$27o$1@dont-email.me>
In reply to#807
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)

[toc] | [prev] | [next] | [standalone]

#809

Fromforough m <fmirkarimzade@gmail.com>
Date2018-02-28 03:06 -0800
Message-ID<f79bd543-002e-4a38-8f74-0727c9f83bb6@googlegroups.com>
In reply to#808
On Tuesday, February 27, 2018 at 4:34:10 PM UTC+3:30, Laurenz Albe wrote:
> 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)

Than you soo much :)

[toc] | [prev] | [next] | [standalone]

#810

Fromforough m <fmirkarimzade@gmail.com>
Date2018-02-28 03:21 -0800
Message-ID<c9fb27ea-edc5-44db-8bb4-92b262192d47@googlegroups.com>
In reply to#808
On Tuesday, February 27, 2018 at 4:34:10 PM UTC+3:30, Laurenz Albe wrote:
> 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)

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

[toc] | [prev] | [next] | [standalone]

#811

FromLaurenz Albe <laurenz@nospam.pn>
Date2018-03-01 10:18 +0000
Message-ID<p78k0s$ani$1@dont-email.me>
In reply to#810
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.

[toc] | [prev] | [next] | [standalone]

#812

Fromforough m <fmirkarimzade@gmail.com>
Date2018-03-05 01:44 -0800
Message-ID<23a909c9-9a4e-462a-89fa-a1efda4fb198@googlegroups.com>
In reply to#811
On Thursday, March 1, 2018 at 1:48:05 PM UTC+3:30, Laurenz Albe wrote:
> 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.

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

[toc] | [prev] | [next] | [standalone]

#813

Fromforough m <fmirkarimzade@gmail.com>
Date2018-03-06 07:47 -0800
Message-ID<efaf09da-16e0-48ff-b4eb-5401cd6be4cd@googlegroups.com>
In reply to#812
On Monday, March 5, 2018 at 1:14:54 PM UTC+3:30, forough m wrote:
> On Thursday, March 1, 2018 at 1:48:05 PM UTC+3:30, Laurenz Albe wrote:
> > 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.
> 
> 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

[toc] | [prev] | [next] | [standalone]

#814

FromLaurenz Albe <laurenz@nospam.pn>
Date2018-03-07 07:48 +0000
Message-ID<p7o5gf$g10$1@dont-email.me>
In reply to#813
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

[toc] | [prev] | [next] | [standalone]

#815

Fromforough m <fmirkarimzade@gmail.com>
Date2018-03-07 00:09 -0800
Message-ID<0b7cb08d-f57c-4d94-8da7-5878edd0f090@googlegroups.com>
In reply to#814
On Wednesday, March 7, 2018 at 11:18:32 AM UTC+3:30, Laurenz Albe wrote:
> 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

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

[toc] | [prev] | [next] | [standalone]

#817

FromLaurenz Albe <laurenz@nospam.pn>
Date2018-03-08 11:36 +0000
Message-ID<p7r77m$snp$1@dont-email.me>
In reply to#815
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.

[toc] | [prev] | [next] | [standalone]

#818

Fromforough m <fmirkarimzade@gmail.com>
Date2018-03-09 21:07 -0800
Message-ID<83cb552b-4cff-4f76-b9c9-49bf26b297c9@googlegroups.com>
In reply to#817
On Thursday, March 8, 2018 at 3:06:23 PM UTC+3:30, Laurenz Albe wrote:
> 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.

Sure, thanks :)

[toc] | [prev] | [next] | [standalone]

#816

Fromforough m <fmirkarimzade@gmail.com>
Date2018-03-07 00:10 -0800
Message-ID<ab25358d-5fc4-4474-a9a3-7d1c06e5df96@googlegroups.com>
In reply to#814
On Wednesday, March 7, 2018 at 11:18:32 AM UTC+3:30, Laurenz Albe wrote:
> 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

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web