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] }
  )

case class Attribute(randomVal: Int)

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?

CodePudding user response：

There's a library that adds Slick support for many different PostgreSQL types: https://github.com/tminglei/slick-pg

CodePudding user response：

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.