Correct value of a json data saved using JPA, spring and kotlin Map

I have a jsonb column in a postgres database where I store a bunch of key/value(1 level) into a column mappped to a a Map<String, Any).

The values are supposed to be a String but in code I’m accepting anything. All saved values was passed as ByteArray(byte[]) and it’s stored without problems.

The stored value is transformed to a String and it’s different from the one expected. For example When I store a mapOf(“verion” to “1.0”.toByteArray()) I’m getting this => ‘{“version”:”MS4w”}’

I can fix the code to convert the byte[] to a String before persisting.

the entity was mapped like this:

@Entity
@Table(name = "MY_TABLE")
data class MyTableEntity(
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "my_id") val idDemandePrestation: Long?,
    @Type(type = "jsonObject") @Column(name = "my_data", columnDefinition = "jsonb", nullable = false) val headers: Map<String, Any>
) 

the table:

CREATE TABLE IF NOT EXISTS MY_TABLE (
    my_id BIGSERIAL PRIMARY KEY,
    my_data JSONB NOT NULL 
);

My question: is there a way to correct all values of the data stored dynamically, exp: update “MS4w” => “1.0” and other values using a query or a procedure?

It seems that the value are encoded in Base64

Thanks in advance.

Answer

Running this function should fix the data values encoded to base64:

CREATE OR REPLACE function update_my_table()
    RETURNS INTEGER
    LANGUAGE plpgsql AS -- language declaration required
$func$
DECLARE
    i            record;
    _key         text;
    _value       text;
    update_query text;
    tmp          text;
BEGIN
    For i in select * from my_table
        LOOP
            FOR _key, _value IN
                SELECT * FROM jsonb_each_text(i.my_data)
                LOOP
                    
                    tmp := convert_from(decode(_value, 'base64'), 'UTF-8');
             
                    update_query := ' update my_table set my_data = jsonb_set(my_data, ''{"' ||
                                    _key || '"}'', ''"' || tmp ||
                                    '"'')  where my_id=' || i.my_id;
                    raise notice '%', update_query;
                    execute update_query;
                END LOOP;
        END LOOP;
    return 0;
END
$func$;