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()
  )

Does this answer your question? Inserting Json objects in PostgreSQL json fields with Anorm — Ossip
– Ossip, Commented Jan 28, 2020 at 20:00
@Ossip It does not. Explicitly wrapping the value in a PGobject produces the same error message. — CPS
– CPS, Commented Jan 28, 2020 at 21:23

cchantep · Accepted Answer · 2020-01-29 13:14:02Z

1

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.

answered Jan 29, 2020 at 13:14

cchantep

9,1763 gold badges32 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CPS Over a year ago

Couple things: - My input is not a json object, but a string representation of a json object. I want to simply insert the string, not explicitly construct an object. The string already has the shape of the data I want. - the string interpolation s is leftover from the actual code, which uses it in ways that don't change the result (I checked) - I don't need the result of the insert - changing it to simply execute() produces the same error. Either way, the table does have a SERIAL primary key, so the default parser of executeInsert should still work.

cchantep Over a year ago

Definitely not: executeInsert() need a parser matching the returned insert key. And Json.parse("{}") used as query parameter is a JsValue

CPS Over a year ago

The JSONB column is not the primary key on the table. It (and many other) has an auto-incrementing SERIAL primary key. Other methods in the project use executeInsert without providing an alternate parser and without explicitly passing a value for the pk at all.

CPS Over a year ago

Apologies, I had left in a RETURNING clause that I had been using to return a case class of the resulting row. In my testing I removed that, but forgot to update the question. I've updated the SQL in the question and provided the table definition.

CPS · Accepted Answer · 2020-01-29 19:18:58Z

0

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

answered Jan 29, 2020 at 19:18

CPS

5361 gold badge9 silver badges19 bronze badges

2 Comments

cchantep Over a year ago

Custom JsValue column is not necessary as already provided by the postgres module (and ::TEXT cast too not necessary as JSONB should work)

CPS Over a year ago

We're rather confusingly using spray-json instead of play for json handling, which it doesn't look like that package provides, so defining the column is necessary.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related