Groups > comp.databases.postgresql > #808

Re: Update Json column

Path	csiph.com!eternal-september.org!feeder.eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From	Laurenz Albe <laurenz@nospam.pn>
Newsgroups	comp.databases.postgresql
Subject	Re: Update Json column
Date	Tue, 27 Feb 2018 13:04:09 -0000 (UTC)
Organization	A noiseless patient Spider
Lines	35
Message-ID	<p73l09$27o$1@dont-email.me> (permalink)
References	<fd35c1ca-2d97-476e-88bd-36c0dd722b41@googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=UTF-8
Content-Transfer-Encoding	8bit
Injection-Date	Tue, 27 Feb 2018 13:04:09 -0000 (UTC)
Injection-Info	reader02.eternal-september.org; posting-host="6f6390130b3b3475cd5067af0d21a94e"; logging-data="2296"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/Zkp9A5JBs6qmXcXw0uMUpXPWeOgj4V/4="
User-Agent	Pan/0.144 (Time is the enemy; 28ab3ba git.gnome.org/pan2)
Cancel-Lock	sha1:MfnYM1AfvIK3SiXUSxXZDg8o59o=
Xref	csiph.com comp.databases.postgresql:808

Show key headers only | View raw

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)

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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

csiph-web