Explicit jsonb type cast in Squeryl

Question

I'm using Squeryl 0.9.5-7 and Postgres 9.4 with jsonb datatype and want to insert some data:

case class Log(id: String, meta: String) //meta will contain json
val logs = table[Log]
logs.insert(Log(randomId, "{\"Hi\": \"I'm a json!\"}"))

But got a typecast error that says "Column meta has jsonb type but expression has character varying type. Rewrite expression or convert it's type."

How can I explicitly cast my String field into jsonb so that raw sql-parameter will look like ?::jsonb?

And then, it's interesting how to write json-queries such as @> or ->> with Squeryl?

have you found a satisfying solution? – Boris Oct 21 '15 at 15:15 — Boris, Oct 21 '15 at 15:15

score 3 · Answer 1 · answered Oct 22 '15 at 09:30

I found a better way, that doesn't require to create a CAST in the database.

If you're using squeryl 0.9.6, you can add an explicit cast in your schema which tells squeryl to explicitly cast your string into a jsonb.

on(logs)(s => declare(
   s.meta is (dbType("jsonb").explicitCast) // enables explicit casting into jsonb
 ))

score 0 · Answer 2 · answered Aug 20 '15 at 15:29

0

With Squeryl 0.9.6 you can register support for your own custom types. Here's an example. For non standard operators, take a look at custom functions

answered Aug 20 '15 at 15:29

Dave Whittaker

3,102
13
14

can you actually implement the cast, i.e. ::json just with custom types? – Boris Oct 21 '15 at 15:07

score 0 · Answer 3 · answered Oct 21 '15 at 15:40

In my experience with postgres 9.4, reading works fine as a Squeryl String but inserting fails with:

ERROR: column "****" is of type json but expression is of type character varying [error] Hint: You will need to rewrite or cast the expression. [error] Position: 166 [error] errorCode: 0, sqlState: 42804

So, the solution I found is to create a 'AS ASSIGNMENT' cast in my postgres database and that does the trick:

CREATE CAST(VARCHAR AS JSON)
WITH INOUT
AS ASSIGNMENT

Explicit jsonb type cast in Squeryl

3 Answers3