From: | Deven Phillips <deven(dot)phillips(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Appending key-value to JSONB tree |
Date: | 2016-02-17 15:47:23 |
Message-ID: | CAJw+4NACq6okpRMYFnHCrnYJJkKOj=0jikH86amafkG3OX=EXw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Magnum | 2016-02-17 16:05:07 | Log Monitoring with PG Admin |
Previous Message | Boyan Botev | 2016-02-17 15:39:15 | Charlotte Postgres User Group |