t9t/jooq-postgresql-json

jOOQ support for PostgreSQL json & jsonb

⚠ 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: #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.

Library version	jOOQ version	Note
4.0.0	3.16.x - 3.18.x	Breaking change, no longer includes a dependency on jooq. Tested with both jOOQ 3.16.20 (JDK 11 and 17), and 3.17.14 and 3.18.5 (JDK 17 only).
3.2.3	3.16.7
3.2.2	3.16.6
3.2.1	3.16.5
3.2.0	3.16.3
3.1.2	3.15.5
3.1.1	3.15.4
3.1.0	3.15.1
3.0.0	3.14.12	Breaking change, upgraded from Java 8 to Java 11. Java 8 no longer supported.

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):

Op	Operand	Description	Method
->	int	Get array element	arrayElement()
->	text	Get object field	fieldByKey()
->>	int	Get array element as text	arrayElementText()
->>	text	Get object field as text	fieldByKeyText()
#>	text[]	Get object at path	objectAtPath()
#>>	text[]	Get object at path as text	objectAtPathText()

Operators available only for jsonb (through JsonbDSL):

Op	Operand	Description	Method
@>	jsonb	Does contain value?	contains()
<@	jsonb	Are entries contained?	containedIn()
?	text	Does the key exist?	hasKey()
?|	text[]	Does any key exist?	hasAnyKey()
?&	text[]	Do all keys exist?	hasAllKeys()
||	jsonb	Concatenate values	concat()
-	text	Delete key or element	delete()
-	text[]	Delete multiple keys or elements	delete()
-	int	Delete array element	deleteElement()
#-	text[]	Delete field for path	deletePath()

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):

Function	Return type	Description	Method
json(b)_array_length	int	Get length of JSON array	arrayLength()
json(b)_extract_path	json/jsonb	Extract object at path (same as #>)	extractPath()
json(b)_extract_path_text	text	Extract object at path as text (same as #>>)	extractPathText()
json(b)_typeof	text	Get the type of a JSON field	typeOf()
json(b)_strip_nulls	json/jsonb	Remove object fields with null values	stripNulls()

Functions only available for jsonb (through JsonbDSL):

Function	Return type	Description	Method
jsonb_pretty	text	Pretty format JSON field	pretty()

References

• jOOQ.org
• PostgreSQL JSON data types

Contributors

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

jooq-postgresql-json's Issues

feat(kotlin): provide operators through extension functions

Hi,

First, I want to thank you for providing this library to simplify the integration of JSONB with JOOQ 👍.

My issue is about providing extension functions for Kotlin projects. Now, jooq supports Kotlin from generator to code, so it could be good to be able to use this kind of syntax:

// PODCAST.METADATA is of type Field<JSONB?>
val youtubeField = PODCAST.METADATA.objectAtPathText("youtube", "channelId")

private fun Field<JSONB?>.objectAtPathText(vararg path: String): Field<String> {
    return DSL.field("{0}#>>{1}", String::class.java, this, DSL.array(*path))
}

If you are interested, I can provide a PR for that if you let me know how do you want to proceed to integrate Kotlin code.

Dependency not uploaded?

Hi, trying to use your repo (in gradle, implementation 'com.github.t9t.jooq:jooq-postgresql-json:0.2.0') getting

  > Could not resolve com.github.t9t.jooq:jooq-postgresql-json:0.2.0.
     Required by:
         project :
      > Could not resolve com.github.t9t.jooq:jooq-postgresql-json:0.2.0.
         > Could not parse POM https://jcenter.bintray.com/com/github/t9t/jooq/jooq-postgresql-json/0.2.0/jooq-postgresql-json-0.2.0.pom
            > Could not find com.github.t9t.jooq:jooq-postgresql-json-parent:0.2.0.

Seems like the parent POM is not uploaded to maven central

Provide module-info.java

At the moment this library can't be depend on it as a java module becasue the library neither contain a module descriptor (module-info.java) nor have Automatic-Module-Name: <module name> entry in the library's MANIFEST.MF.

I propose to add module-info.java (becasue the library is java 9+). So, it'll be possible to depend on in the client's module-info.java

Please deploy correct artifact to maven

jooq-postgresql-json-parent not deployed to maven, please fix it

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.

If you use jOOQ and/or this library a lot and need bug fixes, new features, etc, I invite you to fork the project and publish your own, improved version of this library. I won't invite anyone to take over this repository and library as is, with the same Maven co-ordinates, because I would have to share my personal Sonatype credentials. If you fork the repo and are dedicated to maintaining the fork, I can link to your fork in the project readme.

You may use this issue for any questions, concerns, discussions, etc.

Kotlin extensions not useful with Kotlin codegen

The kotlin code generator generates pretty much all columns as nullable types (to be consistent with SQL), however the extensions in this project are only on non-nullable field types. So in addition to the Field<JSON> extension, Field<JSON?> extensions would also be necessary.

Feature request: JSON string placeholder

From this

JsonbDSL.field("{\"id\": \"1337\"}")

To this

JsonbDSL.field("{\"id\":{0}}", "1337")

Add "Configure the JooQ code generator" to documentation

There is a link for this in the README.md but no actual instructions.

Add JSON Processing Functions

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

It would be very nice to also add JSON processing functions, besides the operators.

• json_array_length(json)
• jsonb_array_length(jsonb)
• json_each(json)
• jsonb_each(jsonb)
• json_each_text(json)
• jsonb_each_text(jsonb)
• json_extract_path(from_json json, VARIADIC path_elems text[])
• jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])
• json_extract_path_text(from_json json, VARIADIC path_elems text[])
• jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])
• json_object_keys(json)
• jsonb_object_keys(jsonb)
• json_populate_record(base anyelement, from_json json)
• jsonb_populate_record(base anyelement, from_json jsonb)
• json_populate_recordset(base anyelement, from_json json)
• jsonb_populate_recordset(base anyelement, from_json jsonb)
• json_array_elements(json)
• jsonb_array_elements(jsonb)
• json_array_elements_text(json)
• jsonb_array_elements_text(jsonb)
• json_typeof(json)
• jsonb_typeof(jsonb)
• json_to_record(json)
• jsonb_to_record(jsonb)
• json_to_recordset(json)
• jsonb_to_recordset(jsonb)
• json_strip_nulls(from_json json)
• jsonb_strip_nulls(from_json jsonb)
• jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
• jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean])
• jsonb_pretty(from_json jsonb)

I should probably focus on the ones that return json, jsonb, and text first as they will be very simple. The ones that return more complex types (such as setof text or record) will be much more difficult, so I will do them later (or even not at all, if it proves to be too time consuming).

Question: Is it still relevant to use this lib with JOOQ 3.12 ?

Hi,

Simple question as I'm considering using JOOQ on a new project where I'm going to use PostgreSQL's JSON types.

I'm totally new to JOOQ, but I stumbled upon jOOQ/jOOQ#7242 and I'm wondering if support to PostgreSQL isn't "native" to JOOQ starting from 3.12.0.

=> Is there still an added value to use jooq-postgresql-json lib if I'm going to start using JOOQ 3.12+ ?

Thanks in advance :-)

Use jOOQ native JSON/JSONB types

Since jOOQ 1.12 there are now native JSON and JSONB types, so the ones in this library can be dropped and replaced with the native types.

See also: https://www.jooq.org/notes#3.12.0

Note that this either will have to be a breaking change in the API, or there should be two different kinds of JsonDSL/JsonbDSL, or those classes need to have overloads for both the native and custom types.