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
