Appending key-value to JSONB tree - Mailing list pgsql-general

From Deven Phillips
Subject Appending key-value to JSONB tree
Date
Msg-id CAJw+4NACq6okpRMYFnHCrnYJJkKOj=0jikH86amafkG3OX=EXw@mail.gmail.com
Whole thread Raw
Responses Re: Appending key-value to JSONB tree  (Deven Phillips <deven.phillips@gmail.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Boyan Botev
Date:
Subject: Charlotte Postgres User Group
Next
From: Alex Magnum
Date:
Subject: Log Monitoring with PG Admin