Search code examples
sqljsonpostgresqlrandomsql-update

postgres random text in jsonb column

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.

Solution

  • 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.