Handling Postgres json datatype in slick, scala

Question

I have a Postgres 'json' column in my schema. This is my column mapping in my code:

def my_col = column[Option[String]]("my_col")

Which doesn't work, the stack trace I get when inserting says:

column "my_col" is of type json but expression is of type character varying

I tried this too:

def my_col = column[Option[String]]("my_col", O.SqlType("json"))

Which also yields the same error.

Possible duplicate of [Mapping postgreSQL JSON column to Hibernate value type](http://stackoverflow.com/questions/15974474/mapping-postgresql-json-column-to-hibernate-value-type) — vvg, Nov 04 '15 at 10:00

score 10 · Answer 1 · answered Jul 06 '19 at 19:46

So I found the the slick-pg solution to be the better way to get this to work. If all you need is JSON support in your code, i.e. you have a DB column that you simply want to represent as a Play JsValue while using Postgres' JSON column type, you just need to write a profile that mixes in the appropriate functionality from the slick-pg package.

Step 1: Add the necessary dependences

libraryDependencies += "com.github.tminglei" %% "slick-pg" % "0.18.0"
libraryDependencies += "com.github.tminglei" %% "slick-pg_play-json" % "0.18.0"

Step 2: Write a profile class

import com.github.tminglei.slickpg._
import slick.basic.Capability
import slick.jdbc.JdbcCapabilities

trait PostgresProfile extends ExPostgresProfile with PgPlayJsonSupport {
  def pgjson = "jsonb"

  override protected def computeCapabilities: Set[Capability] =
    super.computeCapabilities + JdbcCapabilities.insertOrUpdate

  override val api = PostgresJsonSupportAPI

  object PostgresJsonSupportAPI extends API with JsonImplicits
}

object PostgresProfile extends PostgresProfile

Step 3: Use your new profile class

So you'll need to extend HasDatabaseConfigProvider but parameterize it with the trait that you defined in the step #2 above, e.g.

class MyEntityRepository @Inject() (
  protected val dbConfigProvider: DatabaseConfigProvider
  ... // plus whatever other components you need, probably an ExecutionContext
) extends HasDatabaseConfigProvider[PostgresProfile] {

  import PostgresProfile.api._
...
}

Define your column

This is the easy part. In the above class, MyEntityRepository write a private or package private Slick class that extends Table and simply define your column like this:

def someColumnName = column[JsValue]("some_column_name")

And that's it!

score 4 · Answer 2 · answered Jun 19 '18 at 12:25

4

There are two ways:

Use mapping like in answer.
Use slick-pg package.

answered Jun 19 '18 at 12:25

Eugene Lopatkin

2,351
1
22
34

score 3 · Accepted Answer · answered Nov 04 '15 at 09:59

The problem is that there are no standard json data type in java/scala. So you need to write 'wrapper'. By the link below author did it handling json on db level:

http://www.pateldenish.com/2013/05/inserting-json-data-into-postgres-using-jdbc-driver.html

Handling Postgres json datatype in slick, scala

3 Answers3

Step 1: Add the necessary dependences

Step 2: Write a profile class

Step 3: Use your new profile class

Define your column