JOOQ How to convert JSON based on other column val

Let's say I have a table customer(int id, type varchar, preferences jsonb). The type can be REGULAR, PREMIUM etc. Based on the column type value the preferences JSON structure will be different.

While loading a customer record from database if type=REGULAR I want to convert it into RegularCustomerPreferences object type, and if type=PREMIUM I want to convert it into PremiumCustomerPreferences object type.

I have gone through several tutorials on using JOOQ JSON converters/Bindings..but they are one to one mapping and not conditional based (depending on another column value).

What is the ideal way to implement this?

You obviously cannot do this in a type safe way as the type of your preferences column would be Field<RegularCustomerPreferences | PremiumCustomerPreferences> (the union type), and Java doesn't currently support union types. So, you could bind a common CustomerPreferences super type to the column, perhaps, and downcast the value wherever it is consumed.

Binding the super type should be relatively easy. You will be implementing a Binding<Object, CustomerPreferences>, which can handle both RegularCustomerPreferences and PremiumCustomerPreferences values. Specifically, your converter would look something like this:

public Converter<Object, CustomerPreferences> converter() {
    return new Converter<Object, CustomerPreferences>() {
        @Override
        public CustomerPreferences from(Object t) {
            if (some check here to see if this is a regular customer)
                return new RegularCustomerPreferences(t);
            else
                return new PremiumCustomerPreferences(t);
        }

        @Override
        public Object to(CustomerPreferences u) {
            return MyJSONTools.toJSON(u);
        }

        @Override
        public Class<Object> fromType() {
            return Object.class;
        }

        @Override
        public Class<CustomerPreferences> toType() {
            return CustomerPreferences.class;
        }
    };
}

The assumption here is that your JSON content allows for making the decision what type the JSON document should have, redundantly with the type column, because currently, as of version 3.11 and 3.12, jOOQ doesn't support multi-column data type bindings which read several values to base their data type conversion decisions on. This is a pending feature related to: https://github.com/jOOQ/jOOQ/issues/6124