json_extract_array_element_text for strings

[mbrackett]

Compare: select json_extract_array_element_text('["Sandwich", "Omelette", "Tikka Masala"]', 0)

In Redshift, that gets you an unquoted Sandwich, in docker-pgredshift that gets you a double-quoted "Sandwich".

Adding .strip('"') to the json.dumps seems to solve it:

CREATE FUNCTION json_extract_array_element_text(json_array text, array_index int) RETURNS text immutable as $$
    import json
    result = json.loads(json_array)[array_index]
    return json.dumps(result).strip('"')
    $$ LANGUAGE plpythonu;

[mbrackett]

Similar issue in json_extract_path_text, as well as a missing for loop there

CREATE FUNCTION json_extract_path_text(json_string text, VARIADIC path_elems character[]) RETURNS text immutable as $$
    import json
    result = json.loads(json_string)
    for path_elem in path_elems:
        if path_elem not in result: return ""
        result = result[path_elem]
    return json.dumps(result).strip('"')
    $$ LANGUAGE plpythonu;

[shawnpyle]

In addition to a strip on the returning values, a check that the index is valid is needed.

CREATE OR REPLACE FUNCTION json_extract_array_element_text(json_array text, array_index int) RETURNS text immutable as $$
    import json
    items = json.loads(json_array)
    if 0 <= array_index and array_index < len(items):
        return json.dumps(items[array_index]).strip('"')
    else:
        return None
    $$ LANGUAGE plpythonu;