How to implement Postgresql jsonpath type for Rust's diesel ORM

Question

I want to use Postgresql's jsonb_path_exists function with diesel.

I started with the function definition which compiles.

diesel::sql_function! {
    /// https://www.postgresql.org/docs/current/functions-json.html
    fn jsonb_path_exists(
        jsonb: diesel::sql_types::Nullable<diesel::sql_types::Jsonb>,
        path: diesel::sql_types::Text,
    ) -> diesel::sql_types::Bool;
}

Now I get a runtime error when I use the function:

mytable::table.filter(
    jsonb_path_exists(
        activities::activity_object,
        format!(r#"$.**.id ? (@ == "some-uuid")"#),
    ),
)

The above fails with diesel error: function jsonb_path_exists(jsonb, text) does not exist.

Looking at the signature of jsonb_path_exists I figured the problem is with the second argument: diesel::sql_types::Text. It should be a jsonpath not text.

jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

I confirmed that by debug printing the query, replacing double quotes with single quotes and running it manually against the database. That worked.

I am not sure how to define a new diesel SQL type to make diesel correctly treat the String as Postgres path type and emit single quotes. Looking at the existing types I did not find anything I could implement this analogously to.

EDIT:

Adding a new SqlType is a start. However, how to implement AsExpression<JsonPathType> for some struct JsonPath(String) is unclear.

/// Q: How do we know the oid?
/// A: `SELECT typname, oid, typarray FROM pg_type WHERE typname = 'jsonpath';`
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 4072, array_oid = 4073))]
pub struct JsonPathType;

Answer 1

With some help from weiznich on gitter, I figured out the conversion. In a gist, the binary wire format is the path string as bytes prepended with a version.

diesel::sql_function! {
    /// https://www.postgresql.org/docs/current/functions-json.html
    fn jsonb_path_exists(
        jsonb: diesel::sql_types::Nullable<diesel::sql_types::Jsonb>,
        path: custom_sql_types::JsonPath,
    ) -> diesel::sql_types::Bool;
}

pub mod custom_sql_types {
    /// Q: How do we know the oid?
    /// A: `SELECT typname, oid, typarray FROM pg_type WHERE typname = 'jsonpath';`
    #[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
    #[diesel(postgres_type(oid = 4072, array_oid = 4073))]
    pub struct JsonPath;
}

#[derive(Debug, Clone, AsExpression)]
#[diesel(sql_type = custom_sql_types::JsonPath)]
pub struct JsonPath(pub String);

impl ToSql<custom_sql_types::JsonPath, Pg> for JsonPath {
    fn to_sql<'b>(
        &'b self,
        out: &mut diesel::serialize::Output<'b, '_, Pg>,
    ) -> diesel::serialize::Result {
        use std::io::Write;

        // > The type is sent as text in binary mode, so this is almost the same
        // > as the input function, but it's prefixed with a version number so
        // > we can change the binary format sent in future if necessary.
        // > For now, only version 1 is supported.
        // https://github.com/postgres/postgres/blob/d053a879bb360fb72c46de2a013e741d3f7c9e8d/src/backend/utils/adt/jsonpath.c#L113
        const VERSION_NUMBER: u8 = 1;

        let payload = [&[VERSION_NUMBER], self.0.as_bytes()].concat();
        out.write_all(&payload)
            .map(|_| diesel::serialize::IsNull::No)
            .map_err(|e| {
                Box::new(e) as Box<dyn std::error::Error + Send + Sync>
            })
    }
}

I found the correct version in the postgres codebase here:

/*
 * jsonpath type recv function
 *
 * The type is sent as text in binary mode, so this is almost the same
 * as the input function, but it's prefixed with a version number so we
 * can change the binary format sent in future if necessary. For now,
 * only version 1 is supported.
 */
Datum
jsonpath_recv(PG_FUNCTION_ARGS)
{
    StringInfo  buf = (StringInfo) PG_GETARG_POINTER(0);
    int         version = pq_getmsgint(buf, 1);
    char       *str;
    int         nbytes;

    if (version == JSONPATH_VERSION)
        str = pq_getmsgtext(buf, buf->len - buf->cursor, &nbytes);
    else
        elog(ERROR, "unsupported jsonpath version number: %d", version);

    return jsonPathFromCstring(str, nbytes, NULL);
}