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

Question

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?

possible duplicate of [PostgreSQL jsonb, \`?\` and JDBC](http://stackoverflow.com/questions/27573778/postgresql-jsonb-and-jdbc) — cchantep, Jan 21 '15 at 08:52
A specific parameter conversion can be plugged to prepare part of SQL statement and pass JSON values. — cchantep, Jan 21 '15 at 08:55
@applicius It's not a duplicate of [PostgreSQL jsonb, '?' and JDBC](http://stackoverflow.com/questions/27573778/postgresql-jsonb-and-jdbc). My question is about an `insert` statement. — Blackbird, Jan 21 '15 at 12:53

KJay_wer · Answer 1 · 2015-05-21T11:04:13.217

2

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.

edited May 21 '15 at 11:04

answered May 21 '15 at 10:33

KJay_wer

21
3

1

Thanks for your answer! It probably works, but it's not using Anorm. – Blackbird May 21 '15 at 22:14

score 0 · Answer 2 · answered May 11 '17 at 12:06

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

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

2 Answers2