com.github.t9t.jooq.json.JsonbDSL Maven / Gradle / Ivy
Show all versions of jooq-postgresql-json Show documentation
package com.github.t9t.jooq.json;
import org.jooq.Condition;
import org.jooq.Field;
import org.jooq.impl.DSL;
import java.util.Collection;
/**
* Functions for {@code jsonb} PostgreSQL operator support in jOOQ
*
* Reference: https://www.postgresql.org/docs/11/functions-json.html
*/
public final class JsonbDSL {
/**
* Create a jOOQ {@link Field} wrapping a {@link Jsonb} object representing a {@code jsonb} value for the JSON
* string. Note that the JSON is not validated (any formatting errors will only occur when
* interacting with the database).
*
* @param json JSON string
* @return {@code jsonb} {@code Field} for the JSON string
*/
public static Field field(String json) {
return field(Jsonb.of(json));
}
/**
* Create a jOOQ {@link Field} wrapping the {@link Jsonb} object.
*
* @param jsonb {@code Jsonb} object to wrap
* @return {@code jsonb} {@code Field} for the {@code Jsonb} object
*/
public static Field field(Jsonb jsonb) {
return DSL.field("{0}", Jsonb.class, jsonb);
}
/**
* 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"}
*
* @param jsonField A JSON {@code Field} containing an array to get the array element from
* @param index Array index; negative values count from the end
* @return A {@code Field} representing the extracted array element
*/
public static Field arrayElement(Field jsonField, int index) {
return DSL.field("{0}->{1}", Jsonb.class, jsonField, index);
}
/**
* Get JSON array element as {@code text} rather than {@code json(b)} (indexed from zero, negative integers
* count from the end), using the ->> operator
*
* Example: '[1,2,3]'::json->>2
* Example result: 3
*
* @param jsonField A JSON {@code Field} containing an array to get the array element from
* @param index Array index; negative values count from the end
* @return A {@code Field} representing the extracted array element, as text
*/
public static Field arrayElementText(Field jsonField, int index) {
return DSL.field("{0}->>{1}", String.class, jsonField, index);
}
/**
* Get JSON object field by key using the -> operator
*
* Example: '{"a": {"b":"foo"}}'::json->'a'
* Example result: {"b":"foo"}
*
* @param jsonField The JSON {@code Field} to extract the field from
* @param key JSON field key name
* @return A {@code Field} representing the extracted value
*/
public static Field fieldByKey(Field jsonField, String key) {
return DSL.field("{0}->{1}", Jsonb.class, jsonField, key);
}
/**
* Get JSON object field as {@code text} rather than {@code json(b)}, using the ->>
* operator
*
* Example: '{"a":1,"b":2}'::json->>'b'
* Example result: 2
*
* @param jsonField The JSON {@code Field} to extract the field from
* @param key JSON field key name
* @return A {@code Field} representing the extracted array element, as text
*/
public static Field fieldByKeyText(Field jsonField, String key) {
return DSL.field("{0}->>{1}", String.class, jsonField, key);
}
/**
* Get JSON object at specified path using the #> operator
*
* Example: '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'
* Example result: {"c": "foo"}
*
* @param jsonField The JSON {@code Field} to extract the path from
* @param path Path to the the object to return
* @return A {@code Field} representing the object at the specified path
* @see #objectAtPath(Field, Collection)
*/
public static Field objectAtPath(Field jsonField, String... path) {
return DSL.field("{0}#>{1}", Jsonb.class, jsonField, DSL.array(path));
}
/**
* Get JSON object at specified path using the #> operator
*
* Example: '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'
* Example result: {"c": "foo"}
*
* @param jsonField The JSON {@code Field} to extract the path from
* @param path Path to the the object to return
* @return A {@code Field} representing the object at the specified path
* @see #objectAtPath(Field, String...)
*/
public static Field objectAtPath(Field jsonField, Collection path) {
return objectAtPath(jsonField, path.toArray(new String[0]));
}
/**
* Get JSON object at specified path as {@code text} rather than {@code json(b)}, using the #>>
* operator
*
* Example: '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'
* Example result: 3
*
* @param jsonField The JSON {@code Field} to extract the path from
* @param path Path to the the object to return
* @return A {@code Field} representing the object at the specified path, as text
* @see #objectAtPathText(Field, Collection)
*/
public static Field objectAtPathText(Field jsonField, String... path) {
return DSL.field("{0}#>>{1}", String.class, jsonField, DSL.array(path));
}
/**
* Get JSON object at specified path as {@code text} rather than {@code json(b)}, using the #>>
* operator
*
* Example: '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'
* Example result: 3
*
* @param jsonField The JSON {@code Field} to extract the path from
* @param path Path to the the object to return
* @return A {@code Field} representing the object at the specified path, as text
* @see #objectAtPath(Field, String...)
*/
public static Field objectAtPathText(Field jsonField, Collection path) {
return objectAtPathText(jsonField, path.toArray(new String[0]));
}
/**
* Does the {@code left} JSON value contain the {@code right} JSON path/value entries at the top level? Uses the
* {@code @>} operator.
*
* Example: '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
*
* @param left The JSON {@code Field} that should contain {@code right}
* @param right The JSON {@code Field} that should be contained in {@code left}
* @return A {@code Condition} representing whether {@code left} is contained in {@code right}
*/
public static Condition contains(Field left, Field right) {
return DSL.condition("{0} @> {1}", left, right);
}
/**
* Are the {@code left} JSON path/value entries contained at the top level within the {@code right} JSON value?
* Uses the {@code <@} operator.
*
* Example: '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
*
* @param left The JSON {@code Field} that should be contained in {@code right}
* @param right The JSON {@code Field} that should contain {@code left}
* @return A {@code Condition} representing whether {@code right} is contained in {@code left}
*/
public static Condition containedIn(Field left, Field right) {
return DSL.condition("{0} <@ {1}", left, right);
}
/**
* Does the string exist as a top-level key within the JSON value? Uses the {@code ?} operator.
*
* Example: '{"a":1, "b":2}'::jsonb ? 'b'
*
* @param f The JSON {@code Field} that should contain the {@code key}
* @param key The key that should exist at the top level in the JSON
* @return A {@code Condition} representing whether the key is contained in the JSON value
*/
public static Condition hasKey(Field f, String key) {
return DSL.condition("{0} ?? {1}", f, key);
}
/**
* Do any of these array strings exist as top-level keys? Uses the {@code ?|} operator.
*
* Example: '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
*
* @param f The JSON {@code Field} that should contain any of the {@code keys}
* @param keys List of keys that may exist in the JSON value
* @return A {@code Condition} representing whether any of the {@code keys} exist
* @see #hasAnyKey(Field, Collection)
*/
public static Condition hasAnyKey(Field f, String... keys) {
return DSL.condition("{0} ??| {1}", f, DSL.array(keys));
}
/**
* Do any of these array strings exist as top-level keys? Uses the {@code ?|} operator.
*
* Example: '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
*
* @param f The JSON {@code Field} that should contain any of the {@code keys}
* @param keys List of keys that may exist in the JSON value
* @return A {@code Condition} representing whether any of the {@code keys} exist
* @see #hasAnyKey(Field, String...)
*/
public static Condition hasAnyKey(Field f, Collection keys) {
return hasAnyKey(f, keys.toArray(new String[0]));
}
/**
* Do all of these array strings exist as top-level keys? Uses the {@code ?&} operator.
*
* Example: '["a", "b"]'::jsonb ?& array['a', 'b']
*
* @param f The JSON {@code Field} that should contain all of the {@code keys}
* @param keys List of keys that all should exist in the JSON value
* @return A {@code Condition} representing whether all of the {@code keys} exist
* @see #hasAllKeys(Field, Collection)
*/
public static Condition hasAllKeys(Field f, String... keys) {
return DSL.condition("{0} ??& {1}", f, keys);
}
/**
* Do all of these array strings exist as top-level keys? Uses the {@code ?&} operator.
*
* Example: '["a", "b"]'::jsonb ?& array['a', 'b']
*
* @param f The JSON {@code Field} that should contain all of the {@code keys}
* @param keys List of keys that all should exist in the JSON value
* @return A {@code Condition} representing whether all of the {@code keys} exist
* @see #hasAllKeys(Field, String...)
*/
public static Condition hasAllKeys(Field f, Collection keys) {
return hasAllKeys(f, keys.toArray(new String[0]));
}
/**
* Concatenate two {@code jsonb} values into a new {@code jsonb} value using the {@code ||} operator.
*
* Example: '["a", "b"]'::jsonb || '["c", "d"]'::jsonb
* Example result: ["a", "b", "c", "d"]
*
* @param field1 Field to concatenate {@code field2} to
* @param field2 Field to concatenate to {@code field1}
* @return A {@code Field} representing a concatenation of the two JSON fields
*/
public static Field concat(Field field1, Field field2) {
return DSL.field("{0} || {1}", Jsonb.class, field1, field2);
}
/**
* Delete key/value pair or string element from left operand. Key/value pairs are matched based on their
* key value. Uses the {@code -} operator.
*
* Example: '{"a": "b", "c": "d"}'::jsonb - 'a'
* Example result: {"c": "d"}
*
* @param f JSON {@code Field} to delete the key or element from
* @param keyOrElement The key name or element value to delete from the JSON field
* @return A {@code Field} representing the original field with the key or element deleted
*/
public static Field delete(Field f, String keyOrElement) {
return DSL.field("{0} - {1}", Jsonb.class, f, keyOrElement);
}
/**
* Delete multiple key/value pairs or string elements from left operand. Key/value pairs are matched
* based on their key value. Uses the {@code -} operator.
*
* Example: '{"a": "b", "c": "d", "e": "f"}'::jsonb - '{a,c}'::text[]
* Example result: {"e", "f"}
*
* @param f JSON {@code Field} to delete the keys or elements from
* @param keysOrElements The key names or element values to delete from the JSON field
* @return A {@code Field} representing the original field with the keys or elements deleted
*/
public static Field delete(Field f, String... keysOrElements) {
return DSL.field("{0} - {1}", Jsonb.class, f, DSL.array(keysOrElements));
}
/**
* 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 {@code -} operator.
*
* Example: '["a", "b"]'::jsonb - 1
* Example result: ["a"]
*
* @param f JSON {@code Field} containing an array to delete the element from
* @param index Array index to delete; negative values count from the end of the array
* @return A {@code Field} representing the field with the array element removed
*/
public static Field deleteElement(Field f, int index) {
return DSL.field("{0} - {1}", Jsonb.class, f, index);
}
/**
* Delete the field or element with specified path (for JSON arrays, negative integers count from the end). Uses
* the {@code #-} operator.
*
* Example: '["a", {"b":1,"c":2}]'::jsonb #- '{1,b}'
* Example result: ["a", {"c": 2}]
*
* @param f JSON {@code Field} to delete the selected path from
* @param path Path to the JSON element to remove
* @return A {@code Field} representing the field with the chosen path removed
*/
public static Field deletePath(Field f, String... path) {
return DSL.field("{0} #- {1}", Jsonb.class, f, DSL.array(path));
}
/**
* 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
*
* @param jsonField The JSON {@code Field} containing an array to measure the length of
* @return Length of the array
*/
public static Field arrayLength(Field jsonField) {
return DSL.field("jsonb_array_length({0})", Integer.class, jsonField);
}
/**
* Returns JSON value pointed to by {@code path} (equivalent to #> operator, ie.
* {@link #objectAtPath(Field, String...)}).
*
* Example: jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')
* Example result: {"f5":99,"f6":"foo"}
*
* @param jsonField The JSON {@code Field} to extract the path from
* @param path Path to the the object to return
* @return A {@code Field} representing the object at the specified path
* @see #objectAtPath(Field, String...)
* @see #objectAtPath(Field, Collection)
* @see #extractPath(Field, Collection)
*/
public static Field extractPath(Field jsonField, String... path) {
return DSL.field("jsonb_extract_path({0}, VARIADIC {1})", Jsonb.class, jsonField, DSL.array(path));
}
/**
* Returns JSON value pointed to by {@code path} (equivalent to #> operator, ie.
* {@link #objectAtPath(Field, Collection)}).
*
* Example: jsonb_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')
* Example result: {"f5":99,"f6":"foo"}
*
* @param jsonField The JSON {@code Field} to extract the path from
* @param path Path to the the object to return
* @return A {@code Field} representing the object at the specified path
* @see #objectAtPath(Field, String...)
* @see #objectAtPath(Field, Collection)
* @see #extractPath(Field, String...)
*/
public static Field extractPath(Field jsonField, Collection path) {
return extractPath(jsonField, path.toArray(new String[0]));
}
/**
* Returns JSON value pointed to by {@code path} as text (equivalent to #>> operator, ie.
* {@link #objectAtPathText(Field, String...)}).
*
* Example: jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')
* Example result: foo
*
* @param jsonField The JSON {@code Field} to extract the path from
* @param path Path to the the object to return
* @return A {@code Field} representing the object at the specified path, as text
* @see #objectAtPathText(Field, String...)
* @see #objectAtPathText(Field, Collection)
* @see #extractPathText(Field, Collection)
*/
public static Field extractPathText(Field jsonField, String... path) {
return DSL.field("jsonb_extract_path_text({0}, VARIADIC {1})", String.class, jsonField, DSL.array(path));
}
/**
* Returns JSON value pointed to by {@code path} as text (equivalent to #>> operator, ie.
* {@link #objectAtPathText(Field, Collection)}).
*
* Example: jsonb_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')
* Example result: foo
*
* @param jsonField The JSON {@code Field} to extract the path from
* @param path Path to the the object to return
* @return A {@code Field} representing the object at the specified path, as text
* @see #objectAtPathText(Field, String...)
* @see #objectAtPathText(Field, Collection)
* @see #extractPathText(Field, String...)
*/
public static Field extractPathText(Field jsonField, Collection path) {
return extractPathText(jsonField, path.toArray(new String[0]));
}
/**
* Returns the type of the outermost JSON value as a text string. Possible types are {@code object}, {@code array},
* {@code string}, {@code number}, {@code boolean}, and {@code null}.
*
* Example: jsonb_typeof('-123.4')
* Example result: number
*
* @param jsonField The JSON {@code Field} to determine the type of
* @return The JSON type
*/
public static Field typeOf(Field jsonField) {
return DSL.field("jsonb_typeof({0})", String.class, jsonField);
}
/**
* Returns a JSON {@code Field} with all object fields that have {@code null} values omitted. Other {@code 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]
*
* @param jsonField The JSON {@code Field} to remove {@code null} values from
* @return A JSON {@code Field} with {@code null} object fields removed
*/
public static Field stripNulls(Field jsonField) {
return DSL.field("jsonb_strip_nulls({0})", Jsonb.class, jsonField);
}
/**
* Returns the JSON {@code Field} as indented JSON text.
*
* Example: jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')
* Example result:
{@code
* [
* {
* "f1": 1,
* "f2": null
* },
* 2,
* null,
* 3
* ]
* }
*
* @param jsonField The JSON {@code Field} to format
* @return Pretty formatted, intended String representation of the JSON {@code Field}
*/
public static Field pretty(Field jsonField) {
return DSL.field("jsonb_pretty({0})", String.class, jsonField);
}
}