postgres random text in jsonb column

Question

Following is the query I'm using to scrub some fields in the JSONB column. I'm trying to radomize the first and last name so would like to use something like md5(random()::text) as values.

update people set
data = to_jsonb(data) || '{"firstName": "random_text", "lastName": "random_text"}'
where id = 'b3c09005-7afb-4ad6-922d-76078875e59e';

I tried replacing "random_text" with md5(...) but I get an error "DETAIL: Token "md5" is invalid.". I also tried using || to concat but that didn't work either.

Answer 1

You can use a json builder function to generate the json object:

update people set
data = data || jsonb_build_object(
    'firstName', md5(random()::text), 
    'lastName',  md5(random()::text)
)
where id = 'b3c09005-7afb-4ad6-922d-76078875e59e';

It seems like data is of datatype JSONB. If so, there is not need to use to_jsonb() on it; || is sufficient.

Answer 2

You should concatenate the string with your random function and then cast it to jsonb -

select ('{"firstName":"' ||  md5(random()::text) || '", "lastName":"' || md5(random()::text) ||'"}')::jsonb

Above will create a jsonb object with random first and last name.