Iterating over postgres jsonb column

Question

For example if I have a jsonb column without key-value pairs: e.g.

SomeColumn: ["ONE","TWO","THREE"]

And I would like to migrate the data in the column against a migrationEnumObject e.g

MigrationEnumObject = {"ONE": "1", "TWO": "1", "THREE": "3"}

StartPoint: SomeColumn: ["ONE","TWO","THREE"] desired result: SomeColumn: ["1","2","3"] How would I go about this? I'm quite a beginner with the more complex sql jsonb functions, all the examples I could find relied upon having the key-value pair structure in the jsonb column to be able to utilize json.set. Since this is just an array of strings I could not find any examples over how I would migrate this data from startpoint to the desired result with an sql script, e.g. straight via dbeaver.

Answer 1

If you have a column named some_column of type jsonb in table some_table with value '["ONE","TWO","THREE"]' then you can handle array items with query:

select some_column->>0, some_column->>1, some_column->>2 from some_table

Then you can use result of this query to map new values and build new jsonb array like this:

select json_build_array('1', '2', '3')

UPDATE

Resulting script can be something like this:

select array_to_json(array_remove(array[a, b, c], null)) from (select
(case some_column->>0 when 'ONE' then '1' when 'TWO' then '2' when 'THREE' then '3' end) a,
(case some_column->>1 when 'ONE' then '1' when 'TWO' then '2' when 'THREE' then '3' end) b,
(case some_column->>2 when 'ONE' then '1' when 'TWO' then '2' when 'THREE' then '3' end) c
from some_table) as base