Play Framework, Anorm, Postgres and JSON

With the release of Postgres 9.4, JSON has become a first class citizen. JSON objects inside the DB can be indexed and searched as can regular columns in a relational DB.

If you are using Play Framework with Anorm then JSON with Postgres support doesn't come out of the box. On the bright side, it doesn't take much to make it work.

The examples below have been tested with Play Framework 2.3.7, Postgres 9.4.1 and Postgres JDBC Driver 9.4-1201.

Let's say we have a table in the DB of the following:

CREATE TABLE json_store (
   id serial PRIMARY KEY,
   data json
);
 

And here's some regular Scala code with Anorm:

import anorm.SqlParser._
import anorm._
import play.api.db.DB
import play.api.libs.json.JsValue
import play.api.Play.current

case class JsonData(id:Long, data:JsValue)

object JsonData {
 val jsonData=get[Long]("id")~get[JsValue]("data") map {
   case id~data=> JsonData(id, data)
 }

 def insert(data:JsValue):Option[Long]=DB.withConnection { implicit c=>
   SQL"INSERT INTO json_store (data) VALUES ($data)".executeInsert()
 }

 def findById(jsonId:Long):Option[JsonData]=DB.withConnection { implicit c=>
   SQL"SELECT * FROM object_store WHERE id=$jsonId".as(jsonData*).headOption
 }
}


This doesn't compile with 2 errors:

[error]  could not find implicit value for parameter extractor: anorm.Column[play.api.libs.json.JsValue]
[error]  val jsonData=get[Long]("id")~get[JsValue]("data") map {
[error]                                        ^

and

[error]  type mismatch;
[error]  found   : play.api.libs.json.JsValue
[error]  required: anorm.ParameterValue
[error]     SQL"INSERT INTO json_store (data) VALUES ($data)".executeInsert()


To fix the 1st error, we'll add an implicit that will transform a json into a string and inject it into the SQL statement:

implicit object jsonToStatement extends ToStatement[JsValue] {
 def set(s: PreparedStatement, i: Int, json: JsValue):Unit=s.setString(i, Json.stringify(json))
}

implicit object jsonToStatement extends ToStatement[JsValue] {
def set(s: PreparedStatement, i: Int, json: JsValue):Unit={
  val jsonObject=new org.postgresql.util.PGobject()  

jsonObject.setType("json")

jsonObject.setValue(Json.stringify(json)) s.setObject(i, jsonObject)

}

}

And for the 2nd error, we'll add an implicit that will transform a PGobject into JsValue:

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

So now everything compiles, but we do have a runtime error:

[error]    PSQLException: : ERROR: column "data" is of type json but expression is of type character varying


To overcome, we'll add the following cast:

CREATE CAST (VARCHAR AS JSON) WITHOUT FUNCTION AS IMPLICIT;

And that's all you need for adding JSON support in Play Framework with Anorm.