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.
CodePudding user response:
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))