Package com.github.t9t.jooq.json

Class JsonbDSL

    • Constructor Summary

      Constructors 
      Constructor Description
      JsonbDSL()  

    • Method Summary

      All Methods Static Methods Concrete Methods 
      Modifier and Type Method 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 keyOrElement)
      Delete key/value pair or string element from left operand.
      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> 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, 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, 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.JSONB> 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.JSONB> jsonField, Collection<String> path)
      Returns JSON value pointed to by path as text (equivalent to #>> operator, ie.
      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> field​(org.jooq.JSONB jsonb)
      Create a jOOQ Field wrapping the JSONB object.
      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, 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, Collection<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, 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, Collection<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, 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, Collection<String> path)
      Get JSON object at specified path 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> 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> 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.

    • 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

      • 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