Filter Optional JSONB columns mapped to CustomType

[lakshmankollipara]

I have an optional(nullable) JSONB column which is mapped to Scala Case class.

def myColumn: Rep[MyClass] = column[MyClass]("my_column")

Implicit mapping looks like:

case class MyClass(name: String, number: Int, boolean: Boolean)

def implicitJsonMapper[T: ClassTag](implicit format: OFormat[T]): JdbcType[T] with BaseTypedType[T] =
    MappedJdbcType.base[T, JsValue](Json.toJson(_), _.as[T])

implicit val myClassMapper = implicitJsonMapper[MyClass]

How can I filter based on json keys inside "myColumn" column?

Obviously this didn't work: TableQuery[MyTable].filter(_.myColumn +> "boolean" === true)

[tminglei]

@lakshmankollipara here's the codes of PlayJsonImplicits,

  trait PlayJsonImplicits extends PlayJsonCodeGenSupport {
    import utils.JsonUtils.clean
    implicit val playJsonTypeMapper: JdbcType[JsValue] =
      new GenericJdbcType[JsValue](
        pgjson,
        (v) => Json.parse(v),
        (v) => clean(Json.stringify(v)),
        hasLiteralForm = false
      )

    implicit def playJsonColumnExtensionMethods(c: Rep[JsValue]) = {
        new JsonColumnExtensionMethods[JsValue, JsValue](c)
      }
    implicit def playJsonOptionColumnExtensionMethods(c: Rep[Option[JsValue]]) = {
        new JsonColumnExtensionMethods[JsValue, Option[JsValue]](c)
      }
  }

Here, the points are implicit def playJsonColumnExtensionMethods(c: Rep[JsValue]) and implicit def playJsonOptionColumnExtensionMethods(c: Rep[Option[JsValue]]).

So to support TableQuery[MyTable].filter(_.myColumn +> "boolean" === true), you need add definitions like these,

    implicit def myClass JsonColumnExtensionMethods(c: Rep[MyClass]) = {
        new JsonColumnExtensionMethods[MyClass, MyClass](c)
      }
    implicit def myClass JsonOptionColumnExtensionMethods(c: Rep[Option[MyClass]]) = {
        new JsonColumnExtensionMethods[MyClass, Option[MyClass]](c)
      }

I didn't test it. But you can give it a try.

[1gorsh]

Hi, @tminglei just tried but didn't succeed: Have a dto: case class Address(city: String, country: String) (simplified) JSONB column: val addresses = column[Vector[Address]]("addresses", O.SqlType("JSONB")) Added mappers:

  implicit val addrMapper: JdbcType[Address] with BaseTypedType[Address] = {
    val emptyAddress = Address("", "")
    MappedColumnType.base[Address, Json](
      addr => addr.asJson,
      json => json.as[Address].fold(_ => emptyAddress, identity)
    )
  }

  implicit val addressesMapper: JdbcType[Vector[Address]] with BaseTypedType[Vector[Address]] =
    MappedColumnType.base[Vector[Address], Json](
      listOfAddresses => if (listOfAddresses.isEmpty) Json.arr() else listOfAddresses.asJson,
      json => json.as[Vector[Address]].fold(_ => Vector.empty, identity)
    )

and definitions:

    implicit def addressColumnExtensionMethods(c: Rep[Address]): JsonColumnExtensionMethods[Address, Address] = {
      new JsonColumnExtensionMethods[Address, Address](c)
    }
    implicit def addressOptionColumnExtensionMethods(c: Rep[Option[Address]]): JsonColumnExtensionMethods[Address, Option[Address]] = {
      new JsonColumnExtensionMethods[Address, Option[Address]](c)
    }
    implicit def vectorOfAddressesColumnExtensionMethods(c: Rep[Vector[Address]]): JsonColumnExtensionMethods[Address, Vector[Address]] = {
      new JsonColumnExtensionMethods[Address, Vector[Address]](c)
    }

would like to run query like this: filter(table => table.addresses +> "city" == "London".bind) Getting the error: No implicit found for parameter om: OptionMapper2[Address, String, Address, Vector[Address], Boolean, R_]

Where that om should come from?

[tminglei]

@1gorsh this part of your definitions

    implicit def vectorOfAddressesColumnExtensionMethods(c: Rep[Vector[Address]]): JsonColumnExtensionMethods[Address, Vector[Address]] = {
      new JsonColumnExtensionMethods[Address, Vector[Address]](c)
    }

is wrong. It should be

    implicit def vectorOfAddressesColumnExtensionMethods(c: Rep[Vector[Address]]): JsonColumnExtensionMethods[Address, Vector[Address]] = {
      new JsonColumnExtensionMethods[Vector[Address], Vector[Address]](c)
    }

[1gorsh]

Hi, @tminglei Sorry for the late response. Everything is working now. Thank you for the lib!

[tminglei]

@1gorsh ok! 😄