Answering my own question here... The gist is that if you need to add a new key-value pair, you use *jsonb_set* on the non-existent key and then provide the value as the final parameter.. The new stored procedure looks like:
CREATE OR REPLACE FUNCTION set_user_token(email TEXT, expiration INTERVAL) RETURNS TEXT AS $ WITH newtoken AS ( SELECT random_string(32) token, (now()+INTERVAL '6 months') expiry ), updated AS ( SELECT jsonb_set(data::jsonb, (SELECT ARRAY['tokens', token] FROM newtoken), (SELECT to_jsonb(expiry) FROM newtoken)) newdata FROM users WHERE email=$1 ), updatecomplete AS ( UPDATE users SET data=(SELECT newdata FROM updated) WHERE email=$1 ) SELECT jsonb_pretty(token) FROM newtoken $ LANGUAGE SQL; The difficult part for me was figuring out how to build the array which makes of the *path* parameter for *jsonb_set*... Hope this helps others!!! Deven On Wed, Feb 17, 2016 at 10:47 AM, Deven Phillips <deven.phill...@gmail.com> wrote: > I have a "user" document with a key "tokens" and I would like to write a > stored procedure for adding new token key-value pairs to the "tokens" part > of the tree without removing the old values. I have figured out how to > replace the existing value in the "tokens", but I cannot seem to wrap my > head around appending a new key-value pair. Could someone suggest an > approach (using PostgreSQL 9.5 BTW)... > > Here's my existing stored proc: > > CREATE OR REPLACE FUNCTION set_user_token(email TEXT, expiration INTERVAL) > RETURNS TEXT AS $$ > > WITH newtoken AS ( > > SELECT > > jsonb_build_object(random_string(32), (now()+$2)) token > > ), > updated AS ( > > SELECT > > jsonb_set(data::jsonb, '{"tokens"}', (SELECT token FROM newtoken)) newdata > > FROM > > users > > WHERE > > email=$1 > > ), > updatecomplete AS ( > > UPDATE > > cc_users > > SET > > data=(SELECT newdata FROM updated) > > WHERE > > email=$1 > > ) > SELECT jsonb_pretty(token) FROM newtoken $$ > > LANGUAGE SQL; > > Thanks in advance!!! > > Deven Phillips >