Postgresql: How to convert JSONb keys into an ENUM list?

Question

I'm currently cleaning some tables in our application, and we have some JSONb defined in some tables, whose values are just true or false for a given key. Seeing that, i want to get rid of the jsonb, to have instead an enum array, in which i could get the former keys of my jsonb (whose value was true)

For instance, i want to transform this table

CREATE TABLE my_table (
   ...
   my_field   JSONB
   ...
);

to

CREATE TYPE field_enum AS ENUM (
   'key1',
   'key2',
   'key3',
   'key4',
   'key5',
   'key6'
);

CREATE TABLE my_table (
   ...
   my_field   field_enum[] DEFAULT NULL,
   ...
);

And i want the data to be migrated from (for this example)

{"key1": true, "key2": null, "key3": false, "key4": true}

to

['key1','key4']

I'm trying to alter my colum type by doing the data migration by a unique command, which i think could be something of:

ALTER TABLE
    my_table
    ALTER COLUMN
        my_field
    TYPE
        field_enum
    USING __XXXXXX___;

The using part is where i have difficulties, anybody has an idea of how i should alter my column without losing data?

I'm also open to create a new field and rename it after that, and use an UPDATE on the table.

I was thinking of using the jsonb_object_keys function but it first gives me all keys, and not just those whose values are true, and it also gives me an record[] which i don't manage to cast as a enum[].

Even more deeper, but it is not mandatory for me as i can do it as a post-treatment, it may happen that the json keys has to be linked to a enum key that is not the same, lets say that key1 should be converted as NEW_KEY(upper_case and name changing). Do you think it's possible to include in the same postgresql command?

If anybody of you has an idea of what i could do, i would appreciate any help.

Thanks !

X.

Why don't you completely unroll that and just have 6 Boolean columns? That'd be way easier to query for such flags. — sticky bit
I thought about this idea, but given i have 6 JSONB on my table, and around 10 keys by JSONB, an given that we have a react dropdown component that takes ENUM list easily as inputs, it is very convenient and readeable to have 6 ENUM[] fields instead of 60 fields on my table. — Xav

klin klin · Accepted Answer · 2021-02-12T23:21:22

You need a function to convert a json object to your enum array:

create or replace function jsonb_to_field_enums(jsonb)
returns field_enum[] language sql immutable
as $$
    select array_agg(key)::field_enum[]
    from jsonb_each_text($1)
    where value::bool
$$;

Db<>fidlle.

Postgresql: How to convert JSONb keys into an ENUM list?

1 Answers