Re: replacing jsonb field value

On Sat, May 30, 2015 at 9:10 PM, Andreas Kretschmer
<akretschmer(at)spamfence(dot)net> wrote:
> Michael Paquier <michael(dot)paquier(at)gmail(dot)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