error when creating json_extract UDF

aryehgielchinsky

Hello I'm getting the following error when I try to create a function in Vertica.

SQL Error [3854] [42V13]: [Vertica]VJDBC ROLLBACK: Length of a string in a return type must be greater than zero

CREATE or replace FUNCTION dbr.json_extract(col1 VARCHAR, key1 VARCHAR) RETURN varchar AS BEGIN RETURN MAPLOOKUP(MapJSONExtractor(CASE WHEN RIGHT(col1,1)='}' THEN col1 END), key1)::VARCHAR; END;

Any idea what the issue is? I sending the query with dbeaver 7.0.0 and the database version is Vertica Analytic Database v9.3.0-2.
Thanks

Hibiki

This is the known limitation of the UDF.
MapLookup reports an output field width that is equal to the input field width. But, MapJSONExtractor reports an output field width is -1 because the input length is unknown in the UDF. That is why MapLookup also returns -1 as an output field width.
The current workaround is to develop the UDx like as below.
https://github.com/vertica/Vertica-Extension-Packages/pull/38/files