Update Postgres JSON field

[Julian Backes]

Hi all,

I need a little push in the right direction. I'm trying to update a JSON field in Postgres that means adding a new key/value pair to an existing JSON column.

In SQL, I'd do something like UPDATE my_table SET my_column = my_column || '{"key": "value"}'

In jooq, I'd do ctx.update(MY_TABLE).set(MY_TABLE.MY_COLUMN, ??????)

So what is ????? :-) I expect I have to write some plain sql which is ok for me but I don't really know how to do that at this point.

Thanks in advance!!

Julian

[Lukas Eder]

Hi Julian,

You almost got it right. "Plain SQL" is the term to look for in the manual:

http://www.jooq.org/doc/latest/manual/sql-building/plain-sql

There's also a lot of material about this on Stack Overflow.

Here's how you can implement your particular feature:

public static <T> Field<T> concat(Field<T> field, String json) {

    return DSL.field("{0} || {1}", field.getDataType(), field, DSL.val(json));

}

Or, you might even use the out of the box Field.concat() method for this particular case.

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+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.