How do I insert JSON into a postgres table using Anorm?

Question

I'm getting a runtime exception when trying to insert a JSON string into a JSON column. The string I have looks like """{"Events": []}""", the table has a column defined as status JSONB NOT NULL. I can insert the string into the table from the command line no problem. I've defined a method to do the insert as:

    import play.api.libs.json._
    import anorm._
    import anorm.postgresql._

    def createStatus(
      status: String,
      created: LocalDateTime = LocalDateTime.now())(implicit c: SQLConnection): Unit = {
      SQL(s"""
             |INSERT INTO status_feed
             |  (status, created)
             |VALUES
             |  ({status}, {created})
             |""".stripMargin)
        .on(
          'status -> Json.parse("{}"), // n.b. would be Json.parse(status) but this provides a concise error message
          'created -> created)
        .execute()
    }

and calling it gives the following error:

TypeDoesNotMatch(Cannot convert {}: org.postgresql.util.PGobject to String for column ColumnName(status_feed.status,Some(status)))
anorm.AnormException: TypeDoesNotMatch(Cannot convert {}: org.postgresql.util.PGobject to String for column ColumnName(status_feed.status,Some(status)))

I've done loads of searching for this issue but there's nothing about this specific use case that I could find - most of it is pulling out json columns into case classes. I've tried slightly different formats using spray-json's JsValue, play's JsValue, simply passing the string as-is and casting in the query with ::JSONB and they all give the same error.

Update: here is the SQL which created the table:

  CREATE TABLE status_feed (
    id SERIAL PRIMARY KEY,
    status JSONB NOT NULL,
    created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW()
  )

Answer 1

Turns out cchantep was right, it was the parser I was using. The test framework I am using swallowed the stack trace and I assumed the problem was on the insert, but what's actually blowing up is the next line in the test where I use the parser.

The case class and parser were defined as:

case class StatusFeed(
  status: String,
  created: LocalDateTime) {
  val ItemsStatus: Status = status.parseJson.convertTo[Status]
}

object StatusFeed extends DefaultJsonProtocol {
  val fields: String = sqlFields[StatusFeed]() // helper function that results in "created, status"
  // used in SQL as RETURNING ${StatusFeed.fields}
  val parser: RowParser[StatusFeed] =
    Macro.namedParser[StatusFeed](Macro.ColumnNaming.SnakeCase)
  // json formatter for Status
}

As defined the parser attempts to read a JSONB column from the result set into the String status. Changing fields to val fields: String = "created, status::TEXT" resolves the issue, though the cast may be expensive. Alternatively, defining status as a JsValue instead of a String and providing an implicit for anorm (adapted from this answer to use spray-json) fixes the issue:

  implicit def 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.getValue.parseJson)
      case _ =>
        Left(TypeDoesNotMatch(
          s"Cannot convert $value: ${value.asInstanceOf[AnyRef].getClass} to Json for column $qualified"))
    }
  }

Answer 2

The error is not on values given to .executeInsert, but on the parsing of the INSERT result (inserted key).

import java.sql._

// postgres=# CREATE TABLE test(foo JSONB NOT NULL);

val jdbcUrl = "jdbc:postgresql://localhost:32769/postgres"
val props = new java.util.Properties()
props.setProperty("user", "postgres")
props.setProperty("password", "mysecretpassword")

implicit val con = DriverManager.getConnection(jdbcUrl, props)

import anorm._, postgresql._
import play.api.libs.json._

SQL"""INSERT INTO test(foo) VALUES(${Json.obj("foo" -> 1)})""".
  executeInsert(SqlParser.scalar[JsValue].singleOpt)

// Option[play.api.libs.json.JsValue] = Some({"foo":1})

/*
postgres=# SELECT * FROM test ;
    foo     
------------
 {"foo": 1}
 */

BTW, the plain string interpolation is useless.