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

--
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 email to jooq-user+unsubscribe@googlegroups.com.
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

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

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 jooq-user+...@googlegroups.com.

[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

On Sat, Nov 10, 2018 at 4:46 AM Madhu Mohan <gude.ma...@gmail.com> wrote:

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,

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

On Thu, Nov 15, 2018 at 3:06 AM Madhu Mohan <gude.ma...@gmail.com> wrote:

I posted the question here:

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

On Wednesday, 14 November 2018 16:41:26 UTC+8, Madhu Mohan wrote:

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