Re: Appending key-value to JSONB tree

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(dot)phillips(at)gmail(dot)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
>