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?

A specific parameter conversion can be plugged to prepare part of SQL statement and pass JSON values. — cchantep
– cchantep, Commented Jan 21, 2015 at 8:55
@applicius It's not a duplicate of PostgreSQL jsonb, '?' and JDBC. My question is about an insert statement. — Blackbird
– Blackbird, Commented Jan 21, 2015 at 12:53

KJay_wer · Accepted Answer · 2015-05-21 11:04:13Z

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, 2015 at 11:04

answered May 21, 2015 at 10:33

KJay_wer

313 bronze badges

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

1 Comment

Blackbird Over a year ago

Thanks for your answer! It probably works, but it's not using Anorm.

Ricardo · Accepted Answer · 2023-02-04 00:47:39Z

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 4, 2023 at 0:47

Ricardo

4,4085 gold badges43 silver badges58 bronze badges

5 Comments

Ricardo Over a year ago

just tested it with ::json

Blackbird Over a year ago

Sounds like a good solution, thanks! But it's been so long ago, I unfortunately cannot verify it. I've upvoted it though :)

Ricardo Over a year ago

I tested ::json with all the tags on the question and it worked like a charm!

Blackbird Over a year ago

Is it ::json, or ::jsonb as in your code example?

Ricardo Over a year ago

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.

mgosk · Accepted Answer · 2017-05-11 12:06:24Z

0

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

answered May 11, 2017 at 12:06

mgosk

1,87614 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related