[SOLVED] Converting Postgres PGObject to JsValue in Anorm

Converting Postgres PGObject to JsValue in Anorm

This is the query that I am executing in Postgres via JDBC using Anorm:

val sql = s"select row_to_json(t) as result from tablename t;"

The returned object for this query is of type PGObject, which is the default object that JDBC uses when it doesn't recognize the type of the object delivered by the DB.

I want to retrieve this value like this:

    db.withConnection { implicit conn => 
        SQL(sql).as(get[JsValue]("result").single) 
    }

Solution

You have two options.

Option One: Simply change the delivered type by casting the jsonb to a text type.

val sql = s"select row_to_json(t)::text as result from tablename;"

Option Two

Add an implicit conversion in the scope of your code:

implicit val columnToJsValue:Column[JsValue] =
    anorm.Column.nonNull[JsValue] { (value, meta) =>
    val MetaDataItem(qualified, nullable, clazz)=meta
    value match {
        case json: org.postgresql.util.PGobject=> Right(Json.parse(json.getValue))
        case _ => Left(TypeDoesNotMatch(s"Cannot convert $value: ${value.asInstanceOf[AnyRef].getClass} to Json for column $qualified"))
    }
}

I stole that last piece of code from here, and I am not entirely sure about how it works. But it does its job and enables you to use get[JsValue] as a valid conversion type.