postgresqlscalaplayframeworkanorm

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


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?


Solution

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