Inserting Json objects in PostgreSQL json fields with Anorm

Question

How can I pass a JsObject into a json data type field in a PostgreSQL 9.3 database with Anorm without having to cast it as a string?

Given a PostgreSQL 9.3 table such as:

create table profiles
(
  id serial primary key,
  profile json null
);

With Play 2.2, this test succeeds:

package helpers

import anorm._
import org.specs2.mutable._
import org.specs2.runner._
import org.junit.runner._
import play.api.db.DB
import play.api.libs.json._
import play.api.test._

@RunWith(classOf[JUnitRunner])
class AnormTest extends Specification {
  "AnormTest" should {
    "insert a JSON object" in new WithApplication {
      val profile = Json.obj("language" -> "en")
      val sql = SQL("insert into profiles (profile) values (CAST({profile} AS json))")
        .on("profile" -> profile.toString)
      DB.withConnection { implicit c => sql.execute() }
    }
  }
}

But with these lines changed:

      val sql = SQL("insert into profiles (profile) values ({profile})")
        .on("profile" -> profile)

It produces this error:

org.postgresql.util.PSQLException: 
Can't infer the SQL type to use for an instance of play.api.libs.json.JsObject. 
Use setObject() with an explicit Types value to specify the type to use.

Since with Anorm we usually pass the appropriate data types instead of text (for instance, an UUID object for a column of uuid data type), it's not optimal having to convert the JsObject to a string and cast it back to the json data type in the SQL statement.

For an example of this issue and its workaround, please refer to Using PostgreSQL's native JSON support in Play Framework 2.1-RC1.

How can this be avoided with Anorm in order to pass the JsObject directly as a json data type?

Answer 1

For the Play 2.4 and above use the anorm.Object(value: org.postgresql.util.PGobject) class instead of value directly:

val pgObject = new org.postgresql.util.PGobject();
pgObject.setType("json");
pgObject.setValue(profile);
val sql = SQL("insert into profiles (profile) values ({profile})")
    .on("profile" -> anorm.Object(pgObject))