I have a table (postgres 12) with the below schema

ID (Int)  | Meta (JSONb)

1   | {"id": 1, "source": "a"}

I need to update multiple rows to the below format

ID (Int)  | Meta (JSONb)

1   | {"id": "1", "source": "a"}

My only options are

1. Fetch all records on my application server. update the struct and then update each row individually by looping within a transaction. (Im using go-sqlx which unfortunately doesnt seem to have support for batch updates)

There are about 3k records I have to update. so this seems inefficient

The other idea i had is to try something like this

update
    tbl
set meta = jsonb_set(meta, 'id' , meta ->> 'id'::text)
where meta ->> 'source' = 'a'

This unfortunately doesnt seem to work. is there a way for me to get the old column value here -

jsonb_set(meta, 'id' , "how do i get old json key value here?"::text)

i get the below error

function jsonb_set(jsonb, unknown, text) does not exist....

What is the best approach here?

EDIT based on Edonaurds answer and other threads. I modified the query a bit

update
    tbl
set meta = jsonb_set(meta, '{id}', (meta->>'id')::jsonb)
where meta ->> 'Source' = 'a'

While this updates - i still cant seem to convert the ID field into string

i still get back the original row

CodePudding user response：

try this :

update
    tbl
set meta = jsonb_set(meta, '{id}' :: text[] , to_jsonb(meta ->> 'id'))
where meta ->> 'source' = 'a'