Add support for currently unsupported JSON/JSONB operators · Issue #3032 · diese...

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

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

1. Define the operator via infix_operator!(). The contains operator would be defined as following: infix_operator!(KeyExists, " ? ", backend: Pg); These operators can be defined here. If there is already an existing definition, this step could be skipped.
2. Define a new helper type here. Helper types are a tool for diesel users to simplify type definitions. Again if there is already a matching type definition this could be skipped.
3. Add a new ExpressionMethods extension trait for the corresponding types. This only needs to happen once for jsonb and once for jsonb + json. See the PgRangeExpressionMethods trait here as example.
4. Add a new method to the corresponding ExpressionMethods trait. See here for an example for a different operator/type. This definition should:
   • Use the previously defined helper type
   • Contain a documentation with an example
5. Check if compile test output is up to date by running TRYBUILD=overwrite cargo test inside of diesel_compile_tests. (This is required if you've added a new operator based on infix_operator!
6. Submit a PR with the change

Operator list:

• json/jsonb -> integer (indexing by index, returns json/jsonb) **
• json/jsonb -> text (indexing by key name, returns json/jsonb) **
• json/jsonb ->> integer (indexing by index, returns field as text) **
• json/jsonb ->> text (indexing by key name, returns field as text) **
• json/jsonb #> text[] (indexing by path, returns field as json/jsonb) *
• json/jsonb #>> text[] (indexing by path, returns field as text) *
• jsonb @> jsonb (checks if the second value is contained by the first value, returns a boolean) *
• jsonb <@ jsonb (checks if the first value is contained by the second value, returns a boolean) *
• jsonb ? text (checks if a string exists as top level key in the given json, returns a boolean) *
• jsonb ?| text[] (checks if any of the strings exists as top level key in the given json, returns a boolean) *
• jsonb ?& text[] (checks if all of the strings exist as top level key in the given json, returns a boolean) *
• jsonb || jsonb (concats two json values, returns jsonb) *
• jsonb - text (remove the key given as string from the json, returns jsonb) **
• jsonb - text[] (removes all keys given as string from the json, returns jsonb) **
• jsonb - integer (removes the field with the given index from the json, returns jsonb) **
• jsonb #- text[] (removes the given path from the json, returns jsonb) *
• jsonb @? jsonpath (Does JSON path return any item for the specified JSON value?, returns a boolean) ***
• jsonb @@ jsonpath (Returns the result of a JSON path predicate check for the specified JSON value. Only the first item of the result is taken into account. If the result is not Boolean, then NULL is returned., returns a boolean`) ***

Items marked with one * can follow the instructions directly, items marked with ** require additional work to unify the different input types behind a common operator/function call (See here for a general example on how to abstract over expressions of different types), items marked with *** likely require additional design work.

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

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