Add support for currently unsupported postgres json/jsonb functions

[weiznich]

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:

1. 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.
2. If the function is generic, add a helper type definition here. Again see the linked definition for an example for the array_append function.
3. Add a test for #[auto_type] support for the newly added function here
4. Submit a PR with the change

Method list:

• to_json ( anyelement ) → json Converts any SQL value to json. *
• to_jsonb ( anyelement ) → jsonb Converts any SQL value to jsonb. *
• 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 ***
• 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. ***
• json_object ( text[] ) → json Builds a JSON object out of a text array. *
• jsonb_object ( text[] ) → jsonb Builds a JSON object out of a text array. *
• json_object ( keys text[], values text[] ) → json This form of json_object takes keys and values pairwise from separate text arrays. *
• 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. ***
• json_array_length ( json ) → integer Returns the number of elements in the top-level JSON array. *
• 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 *
• json_strip_nulls ( json ) → json Deletes all object fields that have null values from the given JSON value, recursively.*
• 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. *
• jsonb_pretty ( jsonb ) → text Converts the given JSON value to pretty-printed, indented text*
• 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.*
• 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:

• expression IS JSON (suffix operator)
• expression IS NOT JSON (suffix operator)
• expression IS JSON OBJECT (suffix operator)
• expression IS JSON ARRAY (suffix operator)
• expression IS JSON SCALAR (suffix operator)
• expression IS NOT JSON OBJECT (suffix operator)
• expression IS NOT JSON ARRAY (suffix operator)
• expression IS NOT JSON SCALAR (suffix operator)
• Maybe variants with `WITH/WITHOUT UNIQUE $key_array" as infix operators

Please add a comment to this issue if you plan to work on a specific method.

If there is anything unclear about how to add a support for a specific method just ask and we will try to answer your questions.

[aznszn]

doing to_json

[valkrypton]

i'll work on json_object

[wowinter13]

Will work on to_jsonb

[gushul]

I'll work on array_to_json

[zaira-bibi]

Working on json_array_length.

[anna-ahmed19]

Working on json_typeof and jsonb_typeof

[anna-ahmed19]

Working on jsonb_pretty

[zaira-bibi]

Working on json_strip_nulls.

[valkrypton]

ill work on jsonb_object next

[aznszn]

working on jsonb_insert

[valkrypton]

i'll work on jsonb_path_exists

[prkbuilds]

I'll work on row_to_json

[meeshal]

I'll work on expression IS JSON and expression IS NOT JSON

[prkbuilds]

I'll work on json_populate_record & jsonb_populate_record

[meeshal]

I'll work on is_json_object, is_not_json_object, is_json_array, is_not_json_array, is_json_scalar, is_not_json_scalar expression methods

[accestfromim]

I'll work on jsonb_set & jsonb_set_lax

[accestfromim]

Sorry, but the url of postgresql's documents of json_functions provided here has a duplicate ‘l’ in the end？

[j-n-f]

I'm trying to understand how diesel handles optional arguments to these functions.

I think the simplest example is for:

array_to_json ( anyarray [, boolean ] ) → json

Looking at the PR (#4301), I don't see how the optional boolean parameter can be passed. Are we just ignoring optional parameters?

[weiznich]

@j-n-f We define several function instances for this. For the variant with no optional parameter we use the original name, for variants with optional parameter we chose a meaningful name and use the #[sql_name] attribute to generate the right sql. See #4171 for an example.

[calebbourg]

Hello, I would like to take a stab at json_array_elements and jsonb_array_elements

[calebbourg]

Actually, apologies... It looks like json_array_elements and jsonb_array_elements are marked as ***.

Does

We do not want to implement them yet, they are just listed to have a complete list

Still hold true? If so, perhaps I can start with jsonb_set

[weiznich]

@calebbourg That statement is still true. Resolving that will require quite a bit fundamental work on how diesel represents types.

As for jsonb_set: #4357 already implements the variant without optional argument. The variant with optional argument is still missing. You would need to declare a function with a suitable name and the right parameters for that. Afterwards you use the #[sql_name] attribute to set the right function name for constructing the actual query.

[calebbourg]

Here's the first stab. #4385 (apologies if that's already known. Not sure what the process is)