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

3

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

31
3

1

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

score 1 · Accepted Answer · answered Feb 04 '23 at 00:47

1

Borrowing from @KJay_wer, we can use ::jsonb tag with Scala Anorm (Play Framework) too, and convert your JsObject (or other JsValue) into String:

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

        val query = """
             insert into my_table (data)
             values ({data}::jsonb);
             """.stripMargin

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

answered Feb 04 '23 at 00:47

Ricardo

3,696
5
36
50

just tested it with `::json` – Ricardo Feb 04 '23 at 00:51
1

Sounds like a good solution, thanks! But it's been so long ago, I unfortunately cannot verify it. I've upvoted it though :) – Blackbird Feb 09 '23 at 15:53
I tested `::json` with all the tags on the question and it worked like a charm! – Ricardo Feb 11 '23 at 01:16
Is it `::json`, or `::jsonb` as in your code example? – Blackbird Feb 12 '23 at 09:35
In the code example above I put `::jsonb` to match the original question; I used `::json` on my use case, which was slightly different. I assume the solution works for your original case too. – Ricardo Feb 13 '23 at 22:34

score 0 · Answer 3 · 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)

3 Answers3

Linked