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.

Nikhil Patil · Accepted Answer

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.

GMB · Answer

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 sees like data is of datatype JSONB. If so, there is not need to use to_jsonb() on it; you can use || directly.

postgres random text in jsonb column

2 Answers

Add your own answers!

Ask a Question