• Update Json column

    From forough m@21:1/5 to All on Mon Feb 26 22:09:17 2018
    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?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Laurenz Albe@21:1/5 to forough m on Tue Feb 27 13:04:09 2018
    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)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From forough m@21:1/5 to Laurenz Albe on Wed Feb 28 03:06:28 2018
    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 :)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From forough m@21:1/5 to Laurenz Albe on Wed Feb 28 03:21:30 2018
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Laurenz Albe@21:1/5 to forough m on Thu Mar 1 10:18:04 2018
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From forough m@21:1/5 to Laurenz Albe on Mon Mar 5 01:44:52 2018
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From forough m@21:1/5 to forough m on Tue Mar 6 07:47:03 2018
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Laurenz Albe@21:1/5 to forough m on Wed Mar 7 07:48:31 2018
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From forough m@21:1/5 to Laurenz Albe on Wed Mar 7 00:10:56 2018
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From forough m@21:1/5 to Laurenz Albe on Wed Mar 7 00:09:05 2018
    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> ?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Laurenz Albe@21:1/5 to forough m on Thu Mar 8 11:36:22 2018
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From forough m@21:1/5 to Laurenz Albe on Fri Mar 9 21:07:51 2018
    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 :)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)