Inserting data to a PostgreSQL jsonb column via Scala Anorm (in Play Framework)

3
votes

The data column of PostgreSQL table my_table is of format jsonb. I would like to insert a Scala JsObject (or JsValue), but don't know how to do it!

The following code does not compile, because the on function expects json to be a String:

  def add(json: JsObject): Option[Long] = {
    DB.withConnection {
      implicit c =>

        val query = """
             insert into my_table(data)
        values({data});"""

        SQL(query).on(
          "data" -> json
        ).executeInsert()
    }
  }

What is the solution?

2
postgresqlscalaplayframeworkanorm
possible duplicate of PostgreSQL jsonb, `?` and JDBC - cchantep
A specific parameter conversion can be plugged to prepare part of SQL statement and pass JSON values. - cchantep
@applicius It's not a duplicate of PostgreSQL jsonb, '?' and JDBC. My question is about an insert statement. - Blackbird
@Blackbird do you solve this problem ? - mgosk
@mgosk No I didn't solve it. - Blackbird

2 Answers

2
votes

Use ::jsonb tag at the end of a value. eg.

//my scala variables

val jsonDescription: String = "{\"name\":\"ksulr\"}"
val age = 15

//my insert statement
val sql = "INSERT into person(json_description,age) VALUES(?::jsonb,?)"

//prepared statement
val statement = conn.prepareStatement(sql)

//insert values into the sql statement
statement.setString(1,jsonDescription)
statement.setInt(2,age)

statement.executeUpdate()

This will work. It worked for me. Thats how I inserted into a postgres db column of type jsonb.

0
votes

You can use ToStatement converter object:

def add(json: JsObject): Option[Long] = {
   DB.withConnection { implicit c =>       
      val query = """insert into my_table(data) values(${data});"""
      SQL(query).executeInsert()
   }
}

implicit object jsObjectStatement extends ToStatement[JsObject] {
    override def set(s: PreparedStatement, index: Int, v: JsObject): Unit = {
      val jsonObject = new PGobject()
      jsonObject.setType("json")
      jsonObject.setValue(Json.stringify(v))
      s.setObject(index, jsonObject)
    }
  }