How to Insert a json object to a column of type jsonb in Postgres using Slick

Question

I'm trying to insert a json object to a column of type jsonb in Postgres using Slick. Here is what I have tried so far..

implicit val attributesJsonFormat = jsonFormat1(Attribute)

implicit val attributesJsonMapper = MappedColumnType.base[Attribute, String](
    { attribute => attribute.toJson.toString() },
    { column => column.parseJson.convertTo[Attribute] }
  )

Attribute case class..

case class Attribute(randomVal: Int)

Here I'm expecting Slick to do implicit conversions between reads and writes. But instead I'm getting the following error..

column \"attributes\" is of type jsonb but expression is of type character varying\n Hint: You will need to rewrite or cast the expression

I assumed this error occurs because of Slick trying to save the object as a varchar.

Then I created a trigger which will call a function before a insert or update. What this function does is convert the relevant object to jsonb before saving to database.

CREATE OR REPLACE FUNCTION parse_attributes_to_json_in_accounts()
    RETURNS TRIGGER AS
$$
BEGIN
    NEW.attributes = to_jsonb(NEW.attributes);
    RETURN NEW;
END;
$$
    LANGUAGE plpgsql;


CREATE TRIGGER tr_parse_attributes_to_json
    BEFORE INSERT OR UPDATE
    ON accounts
    FOR EACH ROW
EXECUTE PROCEDURE parse_attributes_to_json_in_accounts();


ALTER FUNCTION parse_attributes_to_json_in_accounts() OWNER TO app;

Even after this solution I still get the same error message. I'm guessing Slick is throwing the exception before even reaching to this point? Any idea to get this working?

Answer 1

For anyone who is having a similar issue, you can use native sql with jsonb conversion.

sqlu"INSERT INTO accounts VALUES (${account.id}, (to_jsonb($jsonAttribute) #>> '{}')::jsonb)"

Executing the above query will insert a new record with a jsonb object to attributes column.

More information can be found here.. https://scala-slick.org/doc/3.0.0/sql.html