Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

error when creating json_extract UDF

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.


  • HibikiHibiki Employee

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.