Add missing sqlite json/jsonb functions and operators

[weiznich]

Diesel currently supports the sqlite json/jsonb 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 function via define_sql_function!(). These functions can be defined here in a new functions.rs module. See the linked definition of to_json for an example from the postgres backend as an example. This function should have a short documentation snippet with an example (See the linked sqlite 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 to_json function from the postgres backend for an example.
3. Add a test for #[auto_type] support for the newly added function here
4. Submit a PR with the change

Method list:

• json(json) *
• jsonb(json) *
• json_array(value1, value2, …) **
• jsonb_array(value1, value2, …) **
• json_array_length(json) *
• json_array_length(json, path) *
• json_error_position(json) *
• json_extract(json, path, …) **
• jsonb_extract(json, path, …) **
• json_insert(json, path, value, …) **
• jsonb_insert(json, path, value, …) **
• json_object(label1, value1, …) **
• jsonb_object(label1, value1, …) **
• json_patch(json1, json2) *
• jsonb_patch(json1, json2) *
• json_pretty(json) *
• json_remove(json, path, …) **
• jsonb_remove(json, path, …) **
• json_replace(json,path,value,...) **
• jsonb_replace(json,path,value,...) **
• json_set(json,path,value,...) **
• jsonb_set(json,path,value,...) **
• json_type(json) *
• json_type(json,path) *
• json_valid(json) *
• json_valid(json,flags) *
• json_quote(value) *

There are four aggregate SQL functions:

• json_group_array(value) ***
• jsonb_group_array(value) ***
• json_group_object(label,value) ***
• jsonb_group_object(name,value) ***

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

Items marked with ** are variadic functions on SQL side. Rust does not support such function definitions yet, so we either need to have variants for a certain number of fixed arguments or we need to find another solution.

For items marked with *** the function definition needs to be marked with the #[aggregate] attribute.

Operators:

• json -> path
• json ->>

For operators:

These already exists for the postgres backend here. We need to look for a way to share these impls.

[xuehaonan27]

I would like to take json(json) and jsonb(json)

Edit on Dec 12 2024: pull request for this #4388

Edit on Dec 12 2024: update libsqlite3-sys feature bundled-bindings to bundled on the PR branch.

Edit on Dec 13 2024: using libsqlite3-sys bundled-bindings and changed json/jsonb definitions.

[KekmaTime]

I will work on json_valid(json) & json_type(json)

[xuehaonan27]

May I work on more tasks?

[weiznich]

@xuehaonan27 Sure that would be very welcome