What is the current best method for using Postgres JSONB operators in the DSL?

[Daniel Einspanjer]

I was struggling a bit with this over the weekend. FOO is a table with a
column BAR which is a JSONB type field.
I have the generator hooked up with Gson and the signature for FOO.BAR is a
JsonElement.

For an example, a row in the table might have the jsonb value for bar:
{"a": 1, "b": 2}
And my sourceKey JsonObject would be {"b": 2}

Kept getting errors such as:
WARN Caused by: org.jooq.exception.SQLDialectNotSupportedException: Type
class com.google.gson.JsonObject is not supported in dialect DEFAULT

I tried a few different ways:
.where(DSL.sql("{0} @> {1}", FOO.BAR,
DSL.val(sourceKey)))

.where(DSL.sql("{0} @> {1}", FOO.BAR,
DSL.val(sourceKey)))

.where(DSL.sql("{0} @> {1}::jsonb", FOO.BAR,
DSL.val(sourceKey.toString())))

[Lukas Eder]

Hi Daniel,

The warning message happens because jOOQ doesn't know how to bind your Gson
JsonObject to a JDBC statement when you pass it to the DSL.val() method.
You have several options, but they all work the same way. You need to tell
jOOQ how to bind (or convert) that type. I imagine that you wrote your own
binding:
https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

... and then used the code generator configuration to associate that
binding with your FOO.BAR column:
https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings

So, you could use DSL.val(Object, Field) or val(Object, DataType) to bind
your sourceKey:

-
https://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#val-java.lang.Object-org.jooq.Field-
-
https://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#val-java.lang.Object-org.jooq.DataType-

Next step would be to make this operator reusable:

public static Condition jsonContains(Field<JsonObject> field, JsonObject
value) {
return DSL.condition("{0} @> {1}", field, DSL.val(value, field));
}


And now, completely type safe:

where(jsonContains(FOO.BAR, sourceKey))


I hope this helps,
Lukas

Post by Daniel Einspanjer
I was struggling a bit with this over the weekend. FOO is a table with a
column BAR which is a JSONB type field.
I have the generator hooked up with Gson and the signature for FOO.BAR is
a JsonElement.
{"a": 1, "b": 2}
And my sourceKey JsonObject would be {"b": 2}
WARN Caused by: org.jooq.exception.SQLDialectNotSupportedException: Type
class com.google.gson.JsonObject is not supported in dialect DEFAULT
DSL.val(sourceKey)))
DSL.val(sourceKey)))
DSL.val(sourceKey.toString())))
--
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/d/optout.

[Daniel Einspanjer]

Thank you Lukas, this was exactly what I needed. Basically, I was confused
about what exactly I was supposed to pass in for the DataType parameter to
DSL.val and that kept me from hitting this answer.

-Daniel

Post by Lukas Eder
Hi Daniel,
The warning message happens because jOOQ doesn't know how to bind your
Gson JsonObject to a JDBC statement when you pass it to the DSL.val()
method. You have several options, but they all work the same way. You need
https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings
... and then used the code generator configuration to associate that
https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings
So, you could use DSL.val(Object, Field) or val(Object, DataType) to bind
-
https://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#val-java.lang.Object-org.jooq.Field-
-
https://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#val-java.lang.Object-org.jooq.DataType-
public static Condition jsonContains(Field<JsonObject> field, JsonObject
value) {
}
where(jsonContains(FOO.BAR, sourceKey))
I hope this helps,
Lukas
2017-05-08 6:24 GMT+02:00 Daniel Einspanjer <

Post by Daniel Einspanjer
I was struggling a bit with this over the weekend. FOO is a table with a
column BAR which is a JSONB type field.
I have the generator hooked up with Gson and the signature for FOO.BAR is
a JsonElement.
{"a": 1, "b": 2}
And my sourceKey JsonObject would be {"b": 2}
WARN Caused by: org.jooq.exception.SQLDialectNotSupportedException: Type
class com.google.gson.JsonObject is not supported in dialect DEFAULT
DSL.val(sourceKey)))
DSL.val(sourceKey)))
DSL.val(sourceKey.toString())))

--

Post by Daniel Einspanjer
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the
Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/jooq-user/l-MjmxifiSc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
For more options, visit https://groups.google.com/d/optout.

[Madhu Mohan]

Hi Lukas,
When i tried with JOOQ,
myClass r = create.insertInto(POSTGRES_TABLE1)
.set(POSTGRES_TABLE1.CREATEDDATE, currentTimestamp())
.set(POSTGRES_TABLE1.REQUEST, DSL.val(activity.request, JSONObject))
.returning().fetchOne()

In the table "POSTGRES_TABLE1" column "REQUEST" is jsonb data type. But unable to insert data using JOOQ, getting the following error.
Error:
Type class org.jooq.tools.json.JSONObject is not supported in dialect DEFAULT

[Lukas Eder]

Hi Madhu,

Thanks for your message. You will need to configure a data type binding on
your POSTGRES_TABLE1.REQUEST column through the code generator as explained
here:
https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings

You can then bind any client representation to the PostgreSQL JSONB data
type. I wouldn't use org.jooq.tools.json.JSONObject, if I were you, but
some other third party JSON library.

I hope this helps,
Lukas

Post by Madhu Mohan
Hi Lukas,
When i tried with JOOQ,
myClass r = create.insertInto(POSTGRES_TABLE1)
.set(POSTGRES_TABLE1.CREATEDDATE,
currentTimestamp())
.set(POSTGRES_TABLE1.REQUEST,
DSL.val(activity.request, JSONObject))
.returning().fetchOne()
In the table "POSTGRES_TABLE1" column "REQUEST" is jsonb data type. But
unable to insert data using JOOQ, getting the following error.
Type class org.jooq.tools.json.JSONObject is not supported in dialect DEFAULT
--
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/d/optout.

[Madhu Mohan]

Hi Lukas,

I written data bindings as in the code attached files, can you tell me how
to use the implemented conversion below.
My code is as follows.

MyRecord r = dslCtx.insertInto(PGTABLE1)
.set(PGTABLE1.JSON_COLUMN, PGTABLE1.JSON_COLUMN)

.returning().fetchOne()


Getting the following error.

"message": "ERROR: column \"before\" is of type jsonb but expression is of type character varying\n Hint: You will need to rewrite or cast the expression.\n Position: 238",


Can you help here

Thanks
Madhu

Post by Lukas Eder
Hi Madhu,
Thanks for your message. You will need to configure a data type binding on
your POSTGRES_TABLE1.REQUEST column through the code generator as explained
https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings
You can then bind any client representation to the PostgreSQL JSONB data
type. I wouldn't use org.jooq.tools.json.JSONObject, if I were you, but
some other third party JSON library.
I hope this helps,
Lukas

Post by Madhu Mohan
Hi Lukas,
When i tried with JOOQ,
myClass r = create.insertInto(POSTGRES_TABLE1)
.set(POSTGRES_TABLE1.CREATEDDATE,
currentTimestamp())
.set(POSTGRES_TABLE1.REQUEST,
DSL.val(activity.request, JSONObject))
.returning().fetchOne()
In the table "POSTGRES_TABLE1" column "REQUEST" is jsonb data type. But
unable to insert data using JOOQ, getting the following error.
Type class org.jooq.tools.json.JSONObject is not supported in dialect DEFAULT
--
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/d/optout.

[Madhu Mohan]

I posted the question here:
https://stackoverflow.com/questions/53311203/how-to-insert-a-record-into-postgres-database-with-jooq

Post by Madhu Mohan
Hi Lukas,
I written data bindings as in the code attached files, can you tell me how
to use the implemented conversion below.
My code is as follows.
MyRecord r = dslCtx.insertInto(PGTABLE1)
.set(PGTABLE1.JSON_COLUMN, PGTABLE1.JSON_COLUMN)
.returning().fetchOne()
Getting the following error.
"message": "ERROR: column \"before\" is of type jsonb but expression is of type character varying\n Hint: You will need to rewrite or cast the expression.\n Position: 238",
Can you help here
Thanks
Madhu

Post by Lukas Eder
Hi Madhu,
Thanks for your message. You will need to configure a data type binding
on your POSTGRES_TABLE1.REQUEST column through the code generator as
https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings
You can then bind any client representation to the PostgreSQL JSONB data
type. I wouldn't use org.jooq.tools.json.JSONObject, if I were you, but
some other third party JSON library.
I hope this helps,
Lukas

Post by Madhu Mohan
Hi Lukas,
When i tried with JOOQ,
myClass r = create.insertInto(POSTGRES_TABLE1)
.set(POSTGRES_TABLE1.CREATEDDATE,
currentTimestamp())
.set(POSTGRES_TABLE1.REQUEST,
DSL.val(activity.request, JSONObject))
.returning().fetchOne()
In the table "POSTGRES_TABLE1" column "REQUEST" is jsonb data type. But
unable to insert data using JOOQ, getting the following error.
Type class org.jooq.tools.json.JSONObject is not supported in dialect DEFAULT
--
You received this message because you are subscribed to the Google
Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Hi Madhu,

You've already deleted the question. Have you found the answer?

Post by Madhu Mohan
https://stackoverflow.com/questions/53311203/how-to-insert-a-record-into-postgres-database-with-jooq

Post by Madhu Mohan
Hi Lukas,
I written data bindings as in the code attached files, can you tell me
how to use the implemented conversion below.
My code is as follows.
MyRecord r = dslCtx.insertInto(PGTABLE1)
.set(PGTABLE1.JSON_COLUMN, PGTABLE1.JSON_COLUMN)
.returning().fetchOne()
Getting the following error.
"message": "ERROR: column \"before\" is of type jsonb but expression is of type character varying\n Hint: You will need to rewrite or cast the expression.\n Position: 238",
Can you help here
Thanks
Madhu

Post by Lukas Eder
Hi Madhu,
Thanks for your message. You will need to configure a data type binding
on your POSTGRES_TABLE1.REQUEST column through the code generator as
https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings
You can then bind any client representation to the PostgreSQL JSONB data
type. I wouldn't use org.jooq.tools.json.JSONObject, if I were you, but
some other third party JSON library.
I hope this helps,
Lukas

Post by Madhu Mohan
Hi Lukas,
When i tried with JOOQ,
myClass r = create.insertInto(POSTGRES_TABLE1)
.set(POSTGRES_TABLE1.CREATEDDATE,
currentTimestamp())
.set(POSTGRES_TABLE1.REQUEST,
DSL.val(activity.request, JSONObject))
.returning().fetchOne()
In the table "POSTGRES_TABLE1" column "REQUEST" is jsonb data type. But
unable to insert data using JOOQ, getting the following error.
Type class org.jooq.tools.json.JSONObject is not supported in dialect DEFAULT
--
You received this message because you are subscribed to the Google
Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/d/optout.

[Madhu Mohan]

Hi Lukas,
When i tried with JOOQ,
myClass r = create.insertInto(POSTGRES_
TABLE1)
.set(POSTGRES_TABLE1.CREATEDDATE,
currentTimestamp())
.set(POSTGRES_TABLE1.REQUEST,
DSL.val(activity.request, JSONObject))
.returning().fetchOne()

In the table "POSTGRES_TABLE1" column "REQUEST" is jsonb data type. But
unable to insert data using JOOQ, getting the following error.
Error:
Type class org.jooq.tools.json.JSONObject is not supported in dialect
DEFAULT

Post by Daniel Einspanjer
I was struggling a bit with this over the weekend. FOO is a table with a
column BAR which is a JSONB type field.
I have the generator hooked up with Gson and the signature for FOO.BAR is
a JsonElement.
{"a": 1, "b": 2}
And my sourceKey JsonObject would be {"b": 2}
WARN Caused by: org.jooq.exception.SQLDialectNotSupportedException: Type
class com.google.gson.JsonObject is not supported in dialect DEFAULT
DSL.val(sourceKey)))
DSL.val(sourceKey)))
DSL.val(sourceKey.toString())))