Add JSON[] and JSONB[] support to doobie module

[salamonpavel]

Background

At the moment there is no built-in support for arrays containing json data (JSONB[] and JSONB[]) in doobie module. Custom instance of Put and Get typeclasses had to be implemented in AUL in order to allow for functions using these data types.

Since these data types are widely used in our projects it would be useful to provide instances of necessary type classes as part of the library.

  import cats.Show
  import cats.data.NonEmptyList
  import doobie._
  import io.circe.{Json, ParsingFailure}
  import doobie.util.Get
  import io.circe.jawn.parse
  import org.postgresql.util.PGobject
  import cats.syntax.all._
  import org.postgresql.jdbc.PgArray

import scala.util.{Failure, Success, Try}

package object functions {

  implicit val jsonbArrayPutUsingCirceJson: Put[List[Json]] = {
    Put.Advanced.other[PGobject](
      NonEmptyList.of("jsonb[]")
    ).tcontramap { a =>
      val o = new PGobject
      o.setType("jsonb[]")
      o.setValue(a.map(_.noSpaces).mkString("{", ",", "}"))
      o
    }
  }

  implicit val jsonbArrayPutUsingString: Put[List[String]] = {
    Put.Advanced.other[PGobject](
      NonEmptyList.of("jsonb[]")
    ).tcontramap { a =>
      val o = new PGobject
      o.setType("jsonb[]")
      o.setValue(a.mkString("{", ",", "}"))
      o
    }
  }

  implicit val jsonbArrayGetUsingCirceJson: Get[List[Json]] = {
    implicit val showPgArray: Show[PgArray] = Show.fromToString

    def parsePgArray(a: PgArray): Either[ParsingFailure, List[Json]] = {
      val array = a.getArray.asInstanceOf[List[String]]
      array.map(e => parse(e)).sequence
    }

    Get.Advanced.other[PgArray](
      NonEmptyList.of("jsonb[]")
    ).temap( a =>
      parsePgArray(a).leftMap(_.show)
    )
  }

  implicit val jsonbArrayGetUsingString: Get[List[String]] = {
    implicit val showPgArray: Show[PgArray] = Show.fromToString

    def parsePgArray(a: PgArray): Either[String, List[String]] = {
      Try(a.getArray.asInstanceOf[List[String]]) match {
        case Failure(exception) => Left(exception.toString)
        case Success(value) => Right(value)
      }
    }

    Get.Advanced.other[PgArray](
      NonEmptyList.of("jsonb[]")
    ).temap( a =>
      parsePgArray(a)
    )
  }

}

  // usage - we have either Option[List[Json]] or List[Json]
  val domainsOption: Option[List[Json]] = ???
  val domains: List[Json] = ???

  // argument to sql function
  domainsOption::JSONB[]
  domains::JSONB[]

Doobie currently support provides support only for Json data type from Circe. The above instances will add support for their arrays. The instances can be added also for List[String] provided quotes are properly escaped.