Insert Json objects into Postson json fields using Anorm

Question

How to pass a JsObject to a json data type field in a PostgreSQL 9.3 database using Anorm without having to use it as a string?

For a PostgreSQL 9.3 table, for example:

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

In Play 2.2, this test completed successfully:

 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 have 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 corresponding data types instead of text (for example, a UUID object for a column of a UUID data type), it does not seem to be optimal to convert JsObject to a string and return it to json data type in the SQL statement.

For an example of this problem and its workaround, see Using PostgreSQL's native JSON support in Play Framework 2.1-RC1 .

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

Answer 1

For Play 2.4 and later, use the anorm.Object class (value: org.postgresql.util.PGobject) instead of the 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))