com.github.t9t.jooq.json

Class JsonbDSL

java.lang.Object

com.github.t9t.jooq.json.JsonbDSL

public final class JsonbDSL
extends Object

Functions for jsonb PostgreSQL operator support in jOOQ

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

Constructor Summary

Constructors

Constructor and Description
JsonbDSL()

Method Summary

Modifier and Type	Method and Description
static org.jooq.Field<org.jooq.JSONB>	arrayElement(org.jooq.Field<org.jooq.JSONB> jsonField, int index) Get JSON array element (indexed from zero, negative integers count from the end), using the -> operator
static org.jooq.Field<String>	arrayElementText(org.jooq.Field<org.jooq.JSONB> jsonField, int index) Get JSON array element as text rather than json(b) (indexed from zero, negative integers count from the end), using the ->> operator
static org.jooq.Field<Integer>	arrayLength(org.jooq.Field<org.jooq.JSONB> jsonField) Returns the number of elements in the outermost JSON array.
static org.jooq.Field<org.jooq.JSONB>	concat(org.jooq.Field<org.jooq.JSONB> field1, org.jooq.Field<org.jooq.JSONB> field2) Concatenate two jsonb values into a new jsonb value using the || operator.
static org.jooq.Condition	containedIn(org.jooq.Field<org.jooq.JSONB> left, org.jooq.Field<org.jooq.JSONB> right) Are the left JSON path/value entries contained at the top level within the right JSON value? Uses the <@ operator.
static org.jooq.Condition	contains(org.jooq.Field<org.jooq.JSONB> left, org.jooq.Field<org.jooq.JSONB> right) Does the left JSON value contain the right JSON path/value entries at the top level? Uses the @> operator.
static org.jooq.Field<org.jooq.JSONB>	delete(org.jooq.Field<org.jooq.JSONB> f, String... keysOrElements) Delete multiple key/value pairs or string elements from left operand.
static org.jooq.Field<org.jooq.JSONB>	delete(org.jooq.Field<org.jooq.JSONB> f, String keyOrElement) Delete key/value pair or string element from left operand.
static org.jooq.Field<org.jooq.JSONB>	deleteElement(org.jooq.Field<org.jooq.JSONB> f, int index) Delete the array element with specified index (Negative integers count from the end).
static org.jooq.Field<org.jooq.JSONB>	deletePath(org.jooq.Field<org.jooq.JSONB> f, String... path) Delete the field or element with specified path (for JSON arrays, negative integers count from the end).
static org.jooq.Field<org.jooq.JSONB>	extractPath(org.jooq.Field<org.jooq.JSONB> jsonField, Collection<String> path) Returns JSON value pointed to by path (equivalent to #> operator, ie.
static org.jooq.Field<org.jooq.JSONB>	extractPath(org.jooq.Field<org.jooq.JSONB> jsonField, String... path) Returns JSON value pointed to by path (equivalent to #> operator, ie.
static org.jooq.Field<String>	extractPathText(org.jooq.Field<org.jooq.JSONB> jsonField, Collection<String> path) Returns JSON value pointed to by path as text (equivalent to #>> operator, ie.
static org.jooq.Field<String>	extractPathText(org.jooq.Field<org.jooq.JSONB> jsonField, String... path) Returns JSON value pointed to by path as text (equivalent to #>> operator, ie.
static org.jooq.Field<org.jooq.JSONB>	field(org.jooq.JSONB jsonb) Create a jOOQ Field wrapping the JSONB object.
static org.jooq.Field<org.jooq.JSONB>	field(String json) Create a jOOQ Field wrapping a JSONB object representing a jsonb value for the JSON string.
static org.jooq.Field<org.jooq.JSONB>	fieldByKey(org.jooq.Field<org.jooq.JSONB> jsonField, String key) Get JSON object field by key using the -> operator
static org.jooq.Field<String>	fieldByKeyText(org.jooq.Field<org.jooq.JSONB> jsonField, String key) Get JSON object field as text rather than json(b), using the ->> operator
static org.jooq.Condition	hasAllKeys(org.jooq.Field<org.jooq.JSONB> f, Collection<String> keys) Do all of these array strings exist as top-level keys? Uses the ?& operator.
static org.jooq.Condition	hasAllKeys(org.jooq.Field<org.jooq.JSONB> f, String... keys) Do all of these array strings exist as top-level keys? Uses the ?& operator.
static org.jooq.Condition	hasAnyKey(org.jooq.Field<org.jooq.JSONB> f, Collection<String> keys) Do any of these array strings exist as top-level keys? Uses the ?| operator.
static org.jooq.Condition	hasAnyKey(org.jooq.Field<org.jooq.JSONB> f, String... keys) Do any of these array strings exist as top-level keys? Uses the ?| operator.
static org.jooq.Condition	hasKey(org.jooq.Field<org.jooq.JSONB> f, String key) Does the string exist as a top-level key within the JSON value? Uses the ? operator.
static org.jooq.Field<org.jooq.JSONB>	objectAtPath(org.jooq.Field<org.jooq.JSONB> jsonField, Collection<String> path) Get JSON object at specified path using the #> operator
static org.jooq.Field<org.jooq.JSONB>	objectAtPath(org.jooq.Field<org.jooq.JSONB> jsonField, String... path) Get JSON object at specified path using the #> operator
static org.jooq.Field<String>	objectAtPathText(org.jooq.Field<org.jooq.JSONB> jsonField, Collection<String> path) Get JSON object at specified path as text rather than json(b), using the #>> operator
static org.jooq.Field<String>	objectAtPathText(org.jooq.Field<org.jooq.JSONB> jsonField, String... path) Get JSON object at specified path as text rather than json(b), using the #>> operator
static org.jooq.Field<String>	pretty(org.jooq.Field<org.jooq.JSONB> jsonField) Returns the JSON Field as indented JSON text.
static org.jooq.Field<org.jooq.JSONB>	stripNulls(org.jooq.Field<org.jooq.JSONB> jsonField) Returns a JSON Field with all object fields that have null values omitted.
static org.jooq.Field<String>	typeOf(org.jooq.Field<org.jooq.JSONB> jsonField) Returns the type of the outermost JSON value as a text string.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

JsonbDSL

public JsonbDSL()

Method Detail

field

public static org.jooq.Field<org.jooq.JSONB> field(String json)

Create a jOOQ Field wrapping a JSONB object representing a jsonb value for the JSON string. Note that the JSON is not validated (any formatting errors will only occur when interacting with the database).

Parameters:

json - JSON string

Returns:

jsonb Field for the JSON string

field

public static org.jooq.Field<org.jooq.JSONB> field(org.jooq.JSONB jsonb)

Create a jOOQ Field wrapping the JSONB object.

Parameters:

jsonb - JSONB object to wrap

Returns:

jsonb Field for the JSONB object

arrayElement

public static org.jooq.Field<org.jooq.JSONB> arrayElement(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                          int index)

Get JSON array element (indexed from zero, negative integers count from the end), using the -> operator

Example: '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2

Example result: {"c":"baz"}

Parameters:

jsonField - A JSON Field containing an array to get the array element from

index - Array index; negative values count from the end

Returns:

A Field representing the extracted array element

arrayElementText

public static org.jooq.Field<String> arrayElementText(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                      int index)

Get JSON array element as text rather than json(b) (indexed from zero, negative integers count from the end), using the ->> operator

Example: '[1,2,3]'::json->>2

Example result: 3

Parameters:

jsonField - A JSON Field containing an array to get the array element from

index - Array index; negative values count from the end

Returns:

A Field representing the extracted array element, as text

fieldByKey

public static org.jooq.Field<org.jooq.JSONB> fieldByKey(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                        String key)

Get JSON object field by key using the -> operator

Example: '{"a": {"b":"foo"}}'::json->'a'

Example result: {"b":"foo"}

Parameters:

jsonField - The JSON Field to extract the field from

key - JSON field key name

Returns:

A Field representing the extracted value

fieldByKeyText

public static org.jooq.Field<String> fieldByKeyText(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                    String key)

Get JSON object field as text rather than json(b), using the ->> operator

Example: '{"a":1,"b":2}'::json->>'b'

Example result: 2

Parameters:

jsonField - The JSON Field to extract the field from

key - JSON field key name

Returns:

A Field representing the extracted array element, as text

objectAtPath

public static org.jooq.Field<org.jooq.JSONB> objectAtPath(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                          String... path)

Get JSON object at specified path using the #> operator

Example: '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'

Example result: {"c": "foo"}

Parameters:

jsonField - The JSON Field to extract the path from

path - Path to the the object to return

Returns:

A Field representing the object at the specified path

See Also:

objectAtPath(Field, Collection)

objectAtPath

public static org.jooq.Field<org.jooq.JSONB> objectAtPath(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                          Collection<String> path)

Get JSON object at specified path using the #> operator

Example: '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'

Example result: {"c": "foo"}

Parameters:

jsonField - The JSON Field to extract the path from

path - Path to the the object to return

Returns:

A Field representing the object at the specified path

See Also:

objectAtPath(Field, String...)

objectAtPathText

public static org.jooq.Field<String> objectAtPathText(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                      String... path)

Get JSON object at specified path as text rather than json(b), using the #>> operator

Example: '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

Example result: 3

Parameters:

jsonField - The JSON Field to extract the path from

path - Path to the the object to return

Returns:

A Field representing the object at the specified path, as text

See Also:

objectAtPathText(Field, Collection)

objectAtPathText

public static org.jooq.Field<String> objectAtPathText(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                      Collection<String> path)

Get JSON object at specified path as text rather than json(b), using the #>> operator

Example: '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

Example result: 3

Parameters:

jsonField - The JSON Field to extract the path from

path - Path to the the object to return

Returns:

A Field representing the object at the specified path, as text

See Also:

objectAtPath(Field, String...)

contains

public static org.jooq.Condition contains(org.jooq.Field<org.jooq.JSONB> left,
                                          org.jooq.Field<org.jooq.JSONB> right)

Does the left JSON value contain the right JSON path/value entries at the top level? Uses the @> operator.

Example: '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb

Parameters:

left - The JSON Field that should contain right

right - The JSON Field that should be contained in left

Returns:

A Condition representing whether left is contained in right

containedIn

public static org.jooq.Condition containedIn(org.jooq.Field<org.jooq.JSONB> left,
                                             org.jooq.Field<org.jooq.JSONB> right)

Are the left JSON path/value entries contained at the top level within the right JSON value? Uses the <@ operator.

Example: '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb

Parameters:

left - The JSON Field that should be contained in right

right - The JSON Field that should contain left

Returns:

A Condition representing whether right is contained in left

hasKey

public static org.jooq.Condition hasKey(org.jooq.Field<org.jooq.JSONB> f,
                                        String key)

Does the string exist as a top-level key within the JSON value? Uses the ? operator.

Example: '{"a":1, "b":2}'::jsonb ? 'b'

Parameters:

f - The JSON Field that should contain the key

key - The key that should exist at the top level in the JSON

Returns:

A Condition representing whether the key is contained in the JSON value

hasAnyKey

public static org.jooq.Condition hasAnyKey(org.jooq.Field<org.jooq.JSONB> f,
                                           String... keys)

Do any of these array strings exist as top-level keys? Uses the ?| operator.

Example: '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']

Parameters:

f - The JSON Field that should contain any of the keys

keys - List of keys that may exist in the JSON value

Returns:

A Condition representing whether any of the keys exist

See Also:

hasAnyKey(Field, Collection)

hasAnyKey

public static org.jooq.Condition hasAnyKey(org.jooq.Field<org.jooq.JSONB> f,
                                           Collection<String> keys)

Do any of these array strings exist as top-level keys? Uses the ?| operator.

Example: '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']

Parameters:

f - The JSON Field that should contain any of the keys

keys - List of keys that may exist in the JSON value

Returns:

A Condition representing whether any of the keys exist

See Also:

hasAnyKey(Field, String...)

hasAllKeys

public static org.jooq.Condition hasAllKeys(org.jooq.Field<org.jooq.JSONB> f,
                                            String... keys)

Do all of these array strings exist as top-level keys? Uses the ?& operator.

Example: '["a", "b"]'::jsonb ?& array['a', 'b']

Parameters:

f - The JSON Field that should contain all of the keys

keys - List of keys that all should exist in the JSON value

Returns:

A Condition representing whether all of the keys exist

See Also:

hasAllKeys(Field, Collection)

hasAllKeys

public static org.jooq.Condition hasAllKeys(org.jooq.Field<org.jooq.JSONB> f,
                                            Collection<String> keys)

Do all of these array strings exist as top-level keys? Uses the ?& operator.

Example: '["a", "b"]'::jsonb ?& array['a', 'b']

Parameters:

f - The JSON Field that should contain all of the keys

keys - List of keys that all should exist in the JSON value

Returns:

A Condition representing whether all of the keys exist

See Also:

hasAllKeys(Field, String...)

concat

public static org.jooq.Field<org.jooq.JSONB> concat(org.jooq.Field<org.jooq.JSONB> field1,
                                                    org.jooq.Field<org.jooq.JSONB> field2)

Concatenate two jsonb values into a new jsonb value using the || operator.

Example: '["a", "b"]'::jsonb || '["c", "d"]'::jsonb

Example result: ["a", "b", "c", "d"]

Parameters:

field1 - Field to concatenate field2 to

field2 - Field to concatenate to field1

Returns:

A Field representing a concatenation of the two JSON fields

delete

public static org.jooq.Field<org.jooq.JSONB> delete(org.jooq.Field<org.jooq.JSONB> f,
                                                    String keyOrElement)

Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value. Uses the - operator.

Example: '{"a": "b", "c": "d"}'::jsonb - 'a'

Example result: {"c": "d"}

Parameters:

f - JSON Field to delete the key or element from

keyOrElement - The key name or element value to delete from the JSON field

Returns:

A Field representing the original field with the key or element deleted

delete

public static org.jooq.Field<org.jooq.JSONB> delete(org.jooq.Field<org.jooq.JSONB> f,
                                                    String... keysOrElements)

Delete multiple key/value pairs or string elements from left operand. Key/value pairs are matched based on their key value. Uses the - operator.

Example: '{"a": "b", "c": "d", "e": "f"}'::jsonb - '{a,c}'::text[]

Example result: {"e", "f"}

Parameters:

f - JSON Field to delete the keys or elements from

keysOrElements - The key names or element values to delete from the JSON field

Returns:

A Field representing the original field with the keys or elements deleted

deleteElement

public static org.jooq.Field<org.jooq.JSONB> deleteElement(org.jooq.Field<org.jooq.JSONB> f,
                                                           int index)

Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array. Uses the - operator.

Example: '["a", "b"]'::jsonb - 1

Example result: ["a"]

Parameters:

f - JSON Field containing an array to delete the element from

index - Array index to delete; negative values count from the end of the array

Returns:

A Field representing the field with the array element removed

deletePath

public static org.jooq.Field<org.jooq.JSONB> deletePath(org.jooq.Field<org.jooq.JSONB> f,
                                                        String... path)

Delete the field or element with specified path (for JSON arrays, negative integers count from the end). Uses the #- operator.

Example: '["a", {"b":1,"c":2}]'::jsonb #- '{1,b}'

Example result: ["a", {"c": 2}]

Parameters:

f - JSON Field to delete the selected path from

path - Path to the JSON element to remove

Returns:

A Field representing the field with the chosen path removed

arrayLength

public static org.jooq.Field<Integer> arrayLength(org.jooq.Field<org.jooq.JSONB> jsonField)

Returns the number of elements in the outermost JSON array.

Example: jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')

Example result: 5

Parameters:

jsonField - The JSON Field containing an array to measure the length of

Returns:

Length of the array

extractPath

public static org.jooq.Field<org.jooq.JSONB> extractPath(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                         String... path)

Returns JSON value pointed to by path (equivalent to #> operator, ie. objectAtPath(Field, String...)).

Example: jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')

Example result: {"f5":99,"f6":"foo"}

Parameters:

jsonField - The JSON Field to extract the path from

path - Path to the the object to return

Returns:

A Field representing the object at the specified path

See Also:

objectAtPath(Field, String...), objectAtPath(Field, Collection), extractPath(Field, Collection)

extractPath

public static org.jooq.Field<org.jooq.JSONB> extractPath(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                         Collection<String> path)

Returns JSON value pointed to by path (equivalent to #> operator, ie. objectAtPath(Field, Collection)).

Example: jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')

Example result: {"f5":99,"f6":"foo"}

Parameters:

jsonField - The JSON Field to extract the path from

path - Path to the the object to return

Returns:

A Field representing the object at the specified path

See Also:

objectAtPath(Field, String...), objectAtPath(Field, Collection), extractPath(Field, String...)

extractPathText

public static org.jooq.Field<String> extractPathText(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                     String... path)

Returns JSON value pointed to by path as text (equivalent to #>> operator, ie. objectAtPathText(Field, String...)).

Example: jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')

Example result: foo

Parameters:

jsonField - The JSON Field to extract the path from

path - Path to the the object to return

Returns:

A Field representing the object at the specified path, as text

See Also:

objectAtPathText(Field, String...), objectAtPathText(Field, Collection), extractPathText(Field, Collection)

extractPathText

public static org.jooq.Field<String> extractPathText(org.jooq.Field<org.jooq.JSONB> jsonField,
                                                     Collection<String> path)

Returns JSON value pointed to by path as text (equivalent to #>> operator, ie. objectAtPathText(Field, Collection)).

Example: jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')

Example result: foo

Parameters:

jsonField - The JSON Field to extract the path from

path - Path to the the object to return

Returns:

A Field representing the object at the specified path, as text

See Also:

objectAtPathText(Field, String...), objectAtPathText(Field, Collection), extractPathText(Field, String...)

typeOf

public static org.jooq.Field<String> typeOf(org.jooq.Field<org.jooq.JSONB> jsonField)

Returns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null.

Example: jsonb_typeof('-123.4')

Example result: number

Parameters:

jsonField - The JSON Field to determine the type of

Returns:

The JSON type

stripNulls

public static org.jooq.Field<org.jooq.JSONB> stripNulls(org.jooq.Field<org.jooq.JSONB> jsonField)

Returns a JSON Field with all object fields that have null values omitted. Other null values (eg. in arrays) are untouched.

Example: jsonb_strip_nulls('[{"f1":1,"f2":null},2,null,3]')

Example result: [{"f1":1},2,null,3]

Parameters:

jsonField - The JSON Field to remove null values from

Returns:

A JSON Field with null object fields removed

pretty

public static org.jooq.Field<String> pretty(org.jooq.Field<org.jooq.JSONB> jsonField)

Returns the JSON Field as indented JSON text.

Example: jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')

Example result:

 [
     {
         "f1": 1,
         "f2": null
     },
     2,
     null,
     3
 ]
 

Parameters:

jsonField - The JSON Field to format

Returns:

Pretty formatted, intended String representation of the JSON Field