Search code examples
databasepostgresqlmigrationjsonb

How to migrate from an array of strings to a single integer value (JSONB)

I have a table "documents" with a jsonb column "metadata" which I need to migrate to a different structure. How can I turn this: {"BirthYear": ["1999"]} into this: {"birthYear":1999}

Notice that 1999 is now integer, no longer String.

What I tried so far: update documents set metadata = jsonb_set(metadata #-'{BirthYear}', '{birthYear}', metadata -> 'BirthYear' -> 0) But this code only moves the String value from one place to the other.

Solution

  • Get the value as text, cast to integer and convert to jsonb:

    update documents 
set metadata = jsonb_set(
    metadata #-'{BirthYear}', 
    '{birthYear}', 
    to_jsonb((metadata -> 'BirthYear' ->> 0)::int))