replacing jsonb field value

On Sat, May 30, 2015 at 11:52 AM, john.tiger <john.tigernassau@gmail.com> wrote:

using 9.4.2

suppose we have
create table test (id serial primary key, data jsonb);
insert into test (data) values ({"a":1, "b":2})

want to replace "b" with 3

okay, we are retrieving entire record
res = select * from test where data ->> b = 2

newrec = res
newrec["b" = 3

delete from test where data ->> b= 2
insert into test (data) values (newrec)

is this the best way until upsert arrives ?

Append the new value to it the existing field, jsonb has as property
to enforce key uniqueness, and uses the last value scanned for a given
key.
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Michael Paquier <michael.paquier@gmail.com> wrote:

Append the new value to it the existing field, jsonb has as property
to enforce key uniqueness, and uses the last value scanned for a given
key.

can you show a simple example, how to append a jsonb to an jsonb-field?
Maybe i'm blind, but i can't find how it works.

Thx.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

----- Original Message -----

From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-general@postgresql.org
Cc:
Sent: Saturday, 30 May 2015, 13:10
Subject: Re: [GENERAL] replacing jsonb field value

Michael Paquier <michael.paquier@gmail.com> wrote:

Append the new value to it the existing field, jsonb has as property
to enforce key uniqueness, and uses the last value scanned for a given
key.

can you show a simple example, how to append a jsonb to an jsonb-field?
Maybe i'm blind, but i can't find how it works.

Thx.

Andreas

Prior to 9.5 you can't, I think you have to use something like jsonb_each to unwrap it then wrap it back up again.

The jsonbx extension, which I believe is what ended up in 9.5 has a simple concatenate function (here: https://github.com/erthalion/jsonbx), I also had a go (here: https://github.com/glynastill/pg_jsonb_opx).

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Glyn Astill <glynastill@yahoo.co.uk> wrote:

Prior to 9.5 you can't, I think you have to use something like jsonb_each to unwrap it then wrap it back up again.

The jsonbx extension, which I believe is what ended up in 9.5 has a simple concatenate function (here: https://github.com/erthalion/jsonbx), I also had a go (here: https://github.com/glynastill/pg_jsonb_opx).

Thanks.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, May 30, 2015 at 9:10 PM, Andreas Kretschmer
<akretschmer@spamfence.net> wrote:

Michael Paquier <michael.paquier@gmail.com> wrote:

Append the new value to it the existing field, jsonb has as property
to enforce key uniqueness, and uses the last value scanned for a given
key.

can you show a simple example, how to append a jsonb to an jsonb-field?
Maybe i'm blind, but i can't find how it works.

You need some extra magic to do it in 9.4, for example that (not the
best performer by far that's simple enough):
=# CREATE FUNCTION jsonb_append(jsonb, jsonb)
RETURNS jsonb AS $$
WITH json_union AS
(SELECT * FROM jsonb_each_text($1)
UNION ALL
SELECT * FROM jsonb_each_text($2))
SELECT json_object_agg(key, value)::jsonb FROM json_union;
$$ LANGUAGE SQL;
CREATE FUNCTION
=# SELECT jsonb_append('{"a1":"v1", "a2":"v2"}', '{"a1":"b1"}');
jsonb_append
--------------------------
{"a1": "b1", "a2": "v2"}
(1 row)
Googling would show up more performant functions for sure, usable with
9.4, and there is even jsonbx.
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general