Add support for currently unsupported postgres json/jsonb functions

weiznich commented 2 months ago

Diesel currently supports the postgres array types. We do not provide built-in support for various methods available for these types. This is a tracking issue for adding support for these methods.

The general strategy for adding support for new methods is as following:

Define the operator via define_sql_function!(). These operators can be defined here. See the linked definition of array_append for an example. If there is already an existing definition, this step could be skipped. This function should have a short documentation snippet with an example (See the linked postgres documentation for examples for all of the function, please also add variants with null values, etc). If there is a json and a jsonb variant, please implement both in a single PR.
If the function is generic, add a helper type definition here. Again see the linked definition for an example for the array_append function.
Add a test for #[auto_type] support for the newly added function here
Submit a PR with the change

Method list:

[x] to_json ( anyelement ) → json Converts any SQL value to json. *
[x] to_jsonb ( anyelement ) → jsonb Converts any SQL value to jsonb. *
[x] array_to_json ( anyarray [, boolean ] ) → json Converts an SQL array to a JSON array.*
[ ] json_array ( [ { value_expression [ FORMAT JSON ] } [, ...] ] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]) Constructs a JSON array from either a series of value_expression parameters or from the results of query_expression ***
[ ] json_array ( [ query_expression ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]) Constructs a JSON array from either a series of value_expression parameters or from the results of query_expression ***
[x] row_to_json ( record [, boolean ] ) → json Converts an SQL composite value to a JSON object. **
[ ] json_build_array ( VARIADIC "any" ) → json Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list. ***
[ ] jsonb_build_array ( VARIADIC "any" ) → jsonb Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list. ***
[ ] json_object ( [ { key_expression { VALUE | ':' } value_expression [ FORMAT JSON [ ENCODING UTF8 ] ] }[, ...] ] [ { NULL | ABSENT } ON NULL ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]) Constructs a JSON object of all the key/value pairs given, or an empty object if none are given. ***
[x] json_object ( text[] ) → json Builds a JSON object out of a text array. *
[x] jsonb_object ( text[] ) → jsonb Builds a JSON object out of a text array. *
[x] json_object ( keys text[], values text[] ) → json This form of json_object takes keys and values pairwise from separate text arrays. *
[x] jsonb_object ( keys text[], values text[] ) → jsonb This form of json_object takes keys and values pairwise from separate text arrays. *
[ ] json_array_elements ( json ) → setof json Expands the top-level JSON array into a set of JSON values. ***
[ ] jsonb_array_elements ( jsonb ) → setof jsonb Expands the top-level JSON array into a set of JSON values. ***
[ ] json_array_elements_text ( json ) → setof text Expands the top-level JSON array into a set of text values. ***
[ ] jsonb_array_elements_text ( jsonb ) → setof text Expands the top-level JSON array into a set of text values. ***
[x] json_array_length ( json ) → integer Returns the number of elements in the top-level JSON array. *
[x] jsonb_array_length ( jsonb ) → integer Returns the number of elements in the top-level JSON array. *
[ ] json_each ( json ) → setof record ( key text, value json ) Expands the top-level JSON object into a set of key/value pairs.***
[ ] jsonb_each ( json ) → setof record ( key text, value json ) Expands the top-level JSON object into a set of key/value pairs.***
[ ] json_each_text ( json ) → setof record ( key text, value text ) Expands the top-level JSON object into a set of key/value pairs. The returned values will be of type text. ***
[ ] jsonb_each_text ( jsonb ) → setof record ( key text, value text ) Expands the top-level JSON object into a set of key/value pairs. The returned values will be of type text. ***
[ ] json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json Extracts JSON sub-object at the specified path. ***
[ ] jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb Extracts JSON sub-object at the specified path. ***
[ ] json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text Extracts JSON sub-object at the specified path as text. ***
[ ] jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text Extracts JSON sub-object at the specified path as text. ***
[ ] json_object_keys ( json ) → setof text Returns the set of keys in the top-level JSON object. ***
[ ] jsonb_object_keys ( jsonb ) → setof text Returns the set of keys in the top-level JSON object. ***
[ ] json_populate_record ( base anyelement, from_json json ) → anyelement Expands the top-level JSON object to a row having the composite type of the base argument. *
[ ] jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement Expands the top-level JSON object to a row having the composite type of the base argument. *
[ ] json_populate_recordset ( base anyelement, from_json json ) → setof anyelement Expands the top-level JSON array of objects to a set of rows having the composite type of the base argument ***
[ ] jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement Expands the top-level JSON array of objects to a set of rows having the composite type of the base argument ***
[ ] json_to_record ( json ) → record Expands the top-level JSON object to a row having the composite type defined by an AS clause. ****
[ ] jsonb_to_record ( jsonb ) → record Expands the top-level JSON object to a row having the composite type defined by an AS clause. ****
[ ] json_to_recordset ( json ) → setof record Expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause. ***
[ ] jsonb_to_recordset ( jsonb ) → setof record Expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause. ***
[ ] jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) → jsonb Returns target with the item designated by path replaced by new_value, or with new_value added if create_if_missing is true *
[ ] jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb If new_value is not NULL, behaves identically to jsonb_set. Otherwise behaves according to the value of null_value_treatment which must be one of 'raise_exception', 'use_json_null', 'delete_key', or 'return_target' * (we want an enum for the null_value_treatment parameter)
[ ] jsonb_insert ( target jsonb, path text[], new_value jsonb [, insert_after boolean ] ) → jsonb Returns target with new_value inserted. If the item designated by the path is an array element *
[x] json_strip_nulls ( json ) → json Deletes all object fields that have null values from the given JSON value, recursively.*
[x] jsonb_strip_nulls ( jsonb ) → jsonb Deletes all object fields that have null values from the given JSON value, recursively.*
[ ] jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean Checks whether the JSON path returns any item for the specified JSON value.*
[ ] jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean Returns the result of a JSON path predicate check for the specified JSON value. *
[ ] jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb Returns all JSON items returned by the JSON path for the specified JSON value.***
[ ] jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb Returns all JSON items returned by the JSON path for the specified JSON value, as a JSON array. *
[ ] jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb Returns the first JSON item returned by the JSON path for the specified JSON value. Returns NULL if there are no results. *
[ ] jsonb_path_exists_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. *
[ ] jsonb_path_match_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. *
[ ] jsonb_path_query_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. ***
[ ] jsonb_path_query_array_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. *
[ ] jsonb_path_query_first_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. *
[x] jsonb_pretty ( jsonb ) → text Converts the given JSON value to pretty-printed, indented text*
[x] json_typeof ( json ) → text Returns the type of the top-level JSON value as a text string. Possible types are object, array, string, number, boolean, and null.*
[x] jsonb_typeof ( jsonb ) → text Returns the type of the top-level JSON value as a text string. Possible types are object, array, string, number, boolean, and null.*

For items marked with * the instructions above can be followed as written down

For items marked with ** we want try to use the sql_type::Record type as input type, although I'm not 100% sure if that will work.

For items marked with *** its unclear how to express them with the current tools provided by diesel. We do not want to implement them yet, they are just listed to have a complete list (mostly missing set + variadics support)

Items marked with **** need additional non-straighforward handling for the returned record type as outlined in the postgres documentation (we would need to find a way to generate the cast)

In addition diesel does currently not support the expression IS [ NOT ] JSON [ { VALUE | SCALAR | ARRAY | OBJECT } ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] operator variants. For this we need

Add a new diesel_infix/suffic_operator() definition here
Add a new method constructing that operator to this trait. We want a short documentation snippet with an example usage here.
Run compile tests with cd diesel_compile_tests && TRYBUILD=overwrite cargo test to update the output there

We want variants for: