jOOQ PostgreSQL JSON support

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

Requires at least Java 11.

• 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>3.2.0</version>
</dependency>

Version matrix

This shows only which jOOQ version is used in 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.

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 json (through JsonbDSL):

References

• jOOQ.org
• PostgreSQL JSON data types

Contributors

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