Appending key-value to JSONB tree

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

Responses

Browse pgsql-general by date

  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