Package com.github.t9t.jooq.json

Class JsonDSL

java.lang.Object

com.github.t9t.jooq.json.JsonDSL

public final class JsonDSL
extends Object

Functions for json PostgreSQL operator support in jOOQ

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

Constructor Summary

Constructors

Constructor	Description
JsonDSL()

Method Summary

Modifier and Type	Method	Description
static org.jooq.Field<org.jooq.JSON>	arrayElement(org.jooq.Field<org.jooq.JSON> 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.JSON> 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.JSON> jsonField)	Returns the number of elements in the outermost JSON array.
static org.jooq.Field<org.jooq.JSON>	extractPath(org.jooq.Field<org.jooq.JSON> jsonField, String... path)	Returns JSON value pointed to by path (equivalent to #> operator, ie.
static org.jooq.Field<org.jooq.JSON>	extractPath(org.jooq.Field<org.jooq.JSON> jsonField, Collection<String> path)	Returns JSON value pointed to by path (equivalent to #> operator, ie.
static org.jooq.Field<String>	extractPathText(org.jooq.Field<org.jooq.JSON> jsonField, 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.JSON> jsonField, Collection<String> path)	Returns JSON value pointed to by path as text (equivalent to #>> operator, ie.
static org.jooq.Field<org.jooq.JSON>	field(String json)	Create a jOOQ Field wrapping a JSON object representing a json value for the JSON string.
static org.jooq.Field<org.jooq.JSON>	field(org.jooq.JSON json)	Create a jOOQ Field wrapping the JSON object.
static org.jooq.Field<org.jooq.JSON>	fieldByKey(org.jooq.Field<org.jooq.JSON> jsonField, String key)	Get JSON object field by key using the -> operator
static org.jooq.Field<String>	fieldByKeyText(org.jooq.Field<org.jooq.JSON> jsonField, String key)	Get JSON object field as text rather than json(b), using the ->> operator
static org.jooq.Field<org.jooq.JSON>	objectAtPath(org.jooq.Field<org.jooq.JSON> jsonField, String... path)	Get JSON object at specified path using the #> operator
static org.jooq.Field<org.jooq.JSON>	objectAtPath(org.jooq.Field<org.jooq.JSON> jsonField, Collection<String> path)	Get JSON object at specified path using the #> operator
static org.jooq.Field<String>	objectAtPathText(org.jooq.Field<org.jooq.JSON> jsonField, 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.JSON> jsonField, Collection<String> path)	Get JSON object at specified path as text rather than json(b), using the #>> operator
static org.jooq.Field<org.jooq.JSON>	stripNulls(org.jooq.Field<org.jooq.JSON> 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.JSON> 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

JsonDSL

public JsonDSL()

Method Detail

field

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

Create a jOOQ Field wrapping a JSON object representing a json 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:

json Field for the JSON string

field

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

Create a jOOQ Field wrapping the JSON object.

Parameters:

json - JSON object to wrap

Returns:

json Field for the JSON object

arrayElement

public static org.jooq.Field<org.jooq.JSON> arrayElement(org.jooq.Field<org.jooq.JSON> 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.JSON> 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.JSON> fieldByKey(org.jooq.Field<org.jooq.JSON> 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.JSON> 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.JSON> objectAtPath(org.jooq.Field<org.jooq.JSON> 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.JSON> objectAtPath(org.jooq.Field<org.jooq.JSON> 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.JSON> 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.JSON> 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...)

arrayLength

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

Returns the number of elements in the outermost JSON array.

Example: json_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.JSON> extractPath(org.jooq.Field<org.jooq.JSON> jsonField,
                                                        String... path)

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

Example: json_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.JSON> extractPath(org.jooq.Field<org.jooq.JSON> jsonField,
                                                        Collection<String> path)

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

Example: json_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.JSON> jsonField,
                                                     String... path)

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

Example: json_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.JSON> jsonField,
                                                     Collection<String> path)

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

Example: json_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.JSON> jsonField)

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

Example: json_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.JSON> stripNulls(org.jooq.Field<org.jooq.JSON> jsonField)

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

Example: json_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