Re: Appending key-value to JSONB tree

[Deven Phillips <deven.phillips@xxxxxxxxx>]

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="" 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.phillips@xxxxxxxxx> 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="" newdata FROM updated)

WHERE

email=$1

)
SELECT jsonb_pretty(token) FROM newtoken $$

LANGUAGE SQL;

Thanks in advance!!!

Deven Phillips