⚠ Unmaintained ⚠

Unfortunately, I no longer use jOOQ or jooq-postgresql-json myself, and so I don't feel comfortable maintaining this library anymore. I will no longer test the library when new jOOQ versions come out, and I won't closely follow jOOQ news anymore to keep an eye out for new features to integrate with.

See also: https://github.com/t9t/jooq-postgresql-json/issues/24.

jOOQ PostgreSQL JSON support

Provides jOOQ support for PostgreSQL JSON functions and operators for json and jsonb fields.

Requires at least Java 11.

⚠ Since version 4.0.0 this library does not include a transitive Maven dependency on jooq any more, you have to include both jooq and jooq-postgresql-json in your project to use it.

• Include as a Maven dependency
• Usage
  • Kotlin extension functions
• PostgreSQL json operator support
• Available PostgreSQL json processing functions
• References
• Contributors
• 

Include as a Maven dependency

First, add the following Maven dependency:

<dependency>
  <groupId>com.github.t9t.jooq</groupId>
  <artifactId>jooq-postgresql-json</artifactId>
  <version>4.0.0</version>
</dependency>

jooq-postgresql-json does not include a transitive dependency on jooq, so you have to include that yourself as well.

Version matrix

As for 4.0.0, this shows only which jOOQ versions are explicitly tested with this library. Minor version differences should still be compatible and newer major versions of jOOQ might still work with older versions of this library if nothing changed much in the jOOQ JSON APIs. New releases will only be created when incompatibilities with new jOOQ versions are found and fixed.

See the changelog for more information about what is included in the various releases and the reason for the breaking changes.

Usage

Use the JsonDSL and JsonbDSL classes to access the JSON functions and operators.

For example, to extract a JSON nested property value as text from a json field:

/* Sample JSON:
{
  "data": {
    "productCode": "Z-5521"
  }
}
*/
String productCode = dsl.select(JsonDSL.extractPathText(MY_TABLE.DATA_FIELD, "data", "productCode"))
    .from(MY_TABLE).fetchOneInto(String.class);

Or for example using the @> operator to update a row of which a jsonb field contains a certain id:

/* Sample JSON:
{
  "id": "1337",
  "name": "The Hitchhiker's Guide to the Galaxy"
}
*/
dsl.update(MY_TABLE)
    .set(MY_TABLE.RATING, 100)
    .where(JsonbDSL.contains(MY_TABLE.DATA_FIELD, JsonbDSL.field("{\"id\": \"1337\"}")))
    .execute()

• JsonDSL Javadoc
• JsonbDSL Javadoc

Kotlin

Kotlin extension functions are available for Field<JSON?> and Field<JSONB?>. That means that instead of something like JsonDSL.extractPathText(MY_TABLE.DATA_FIELD, "data", "productCode") you can instead write: MY_TABLE.DATA_FIELD.extractPathText("data", "productCode").

The extension functions are available in the following packages:

• com.github.t9t.jooq.json.json
• com.github.t9t.jooq.json.jsonb

The names of extension functions match the names of the methods on JsonDSL and JsonbDSL, except for concat and contains, which are called concatJson and containsJson respectively to prevent clashes with existing methods of Field.

PostgreSQL json operator support

Reference: https://www.postgresql.org/docs/11/functions-json.html

Operators available for both json (through JsonDSL) and jsonb (through JsonbDSL):

Operators available only for jsonb (through JsonbDSL):

Available PostgreSQL json processing functions

Reference: https://www.postgresql.org/docs/11/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

Processing functions available for both json (through JsonDSL) and jsonb (through JsonbDSL):

Functions only available for jsonb (through JsonbDSL):

References

• jOOQ.org
• PostgreSQL JSON data types

Contributors

• @davinkevin was kind enough to implement Kotlin extension functions (#11 and #12)!