feat: Function to mimick Postgres `json_extract_path_text`

[alexanderbianchi]

In Postgres, json_extract_path_text will return a string value without the surrounding double quotes, and any other values as casted strings. Paths not found become NULL's.

https://www.postgresql.org/docs/9.3/functions-json.html

I tried using json_as_text but it fails on cases where the path is not found.

Locally I have vendored the JsonPath and created this function which is giving me the expected behavior.

If there is no way of getting this desired behavior with the existing tools, I could publish my UDF. It's marginally redundant behavior, so I wonder if theres an easier way to factor things.

/// This function mimics the behavior of PostgreSQL's `json_extract_path_text`,
/// which returns the value at a given JSON path as text or NULL if not found.
/// Non-string values are returned as their JSON representation.
fn jiter_json_extract_path_text(
    opt_json: Option<&str>,
    path: &[JsonPath],
) -> Result<Option<String>, jiter::JiterError> {
    if let Some((mut jiter, peek)) = jiter_json_find(opt_json, path) {
        match peek {
            Peek::Null => {
                jiter.known_null()?;
                // The value is JSON null; Postgres returns SQL NULL in this case
                Ok(None)
            }
            Peek::String => {
                let s = jiter.known_str()?;
                Ok(Some(s.to_owned()))
            }
            _ => {
                // For other types (number, bool, object, array), we return their textual representation.
                let start = jiter.current_index();
                jiter.known_skip(peek)?;
                let slice = jiter.slice_to_current(start);
                match String::from_utf8(slice.to_vec()) {
                    Ok(val_str) => Ok(Some(val_str)),
                    Err(_) => Ok(None),
                }
            }
        }
    } else {
        // Path not found; return NULL
        Ok(None)
    }
}

Side note: Could JsonPath be published with the crate in a common package?

[davidhewitt]

Sorry for the slow reply!

I tried using json_as_text but it fails on cases where the path is not found.

What do you mean by "fails"? I think it should be returning null exactly as your code above does.