Appending key-value to JSONB tree
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
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.phillips@gmail.com>
wrote:
Show quoted text
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