How to use jsonb_set with "WITH AS" variable?

Question

I am trying to use jsonb_set to change a single attribute of a jsonb object within my Postgres table.

I am using WITH random_string AS to set the variable random_string to a random hexidecimal value and then pass that string into my UPDATE query. However, it is not working.

This is the code I am using:

WITH random_string AS (SELECT  substr(md5(random()::text), 0, 25)::varchar)

UPDATE 
    teams 
SET 
    profile = jsonb_set(profile, '{api_key}', random_string)
WHERE
    team_id="abc123";

The error I get seems to think I am trying to access a column that does not exist, because this is how you would normally reference a column.

Postgres query failed, PostgresPlugin query failed to execute: error: column "random_string" does not exist

Question: How do I use my random_string variable in the jsonb_set function to update this attribute?

Answer 1

Three issues. First, WITH statement gives a table-like result, not a variable. Define a table with a single column and use its name in the FROM clause in UPDATE. Next, the third argument of jsonb_set() is jsonb, use to_jsonb(). And last, a proper text literal is in single quotes 'abc123'.

WITH var(random_string) AS (SELECT substr(md5(random()::text), 0, 25)::varchar)

UPDATE 
    teams 
SET 
    profile = jsonb_set(profile, '{api_key}', to_jsonb(random_string))
FROM
    var
WHERE
    team_id = 'abc123';