CODEWITHSUNDEEP
sqlpostgresqljsonb
Manolo
Manolo

Reputation: 1676

Postgres, set value in jsonb structure to a return type of a function

I have a table CUSTOMER with a column DATA of type jsonb, where the data looks like this

{
  "ADDRESS": {
    "city": "Berlin",
    "surname": "BRANDT",
    "firstName": "ANA"
  }
}

I need to update DATA in all rows such that the value of surname is predictably scrambled.

So e.g. say that I have a function scramble(..) that takes a string, or a jsonb object, how can I write an UPDATE statement that uses that function to accomplish this? That is, for each row I want to replace the value of surname, with the return value of scramble(DATA -> 'ADDRESS' -> 'surname').

I know about jsonb_set, but all the examples I've seen sets the new value explicitly, and not with a function.

Upvotes: 1

Views: 275

Answers (2)

Jim Jones
Jim Jones

Reputation: 19653

This query might be what you're looking for:

WITH customer(data) AS (
  VALUES ('{
  "ADDRESS": {
    "city": "Berlin",
    "surname": "BRANDT",
    "firstName": "ANA"
  }
}'::jsonb)
) 
SELECT 
  jsonb_set(data,'{ADDRESS,surname}',
    to_jsonb(lower(data->'ADDRESS'->>'surname')))
FROM customer;

                                jsonb_set                                 
--------------------------------------------------------------------------
 {"ADDRESS": {"city": "Berlin", "surname": "brandt", "firstName": "ANA"}}
(1 Zeile)

NOTE: The lower() function is just to give you an idea. Replace it with your scrumble() function.

Upvotes: 1

Mike Organek
Mike Organek

Reputation: 12494

It would be along the lines of:

update customer
   set data = jsonb_set(
                data, 
                '{ADDRESS,surname}',
                to_jsonb(scramble(data->'ADDRESS'->>'surname')::text)
              );

Casting the return from scramble() to text may be unnecessary.

Upvotes: 1

Related Questions