Syntax for transform with expression on value, keeping column name same as field name

Sergey_Cherepan_1

Hi,

User have JSON:

{"modifier": "mod:VAL"}

User wants to load string from 4th char. User insist on having column names same as in JSON.

Solution is trivial, using transform, with filler and expression on filler:

/opt/vertica/bin/vsql -h vdb.vertica.com -U dbadmin -w supersecret -a << +++
drop table public.load_test;
create table public.load_test (modifier varchar(5));
copy public.load_test (modifier filler varchar, modifier as substr(modifier, 4)) from stdin parser fjsonparser() abort on error;
{"modifier": "mod:VAL"}
\.
+++

That gives an error:

ERROR 2671:  Column reference "modifier" is ambiguous
DETAIL:  Reference "modifier" could refer to either "public.load_test.modifier" or "*FILLER*.modifier"

Looking deeper, Vertica is not allowing to declare filler with same name as table column name:

copy public.load_test ("modifier" filler varchar) from stdin parser fjsonparser() abort on error;
ERROR 2671:  Column reference "modifier" is ambiguous
DETAIL:  Reference "modifier" could refer to either "public.load_test.modifier" or "*FILLER*.modifier"

DETAIL gives a hint that I can refer to filler column as "FILLER.modifier". My attempts to find correct syntax were unsuccessful. Look on documentation was fruitless.

Can you ask development, what would be correct transform syntax. They should be able to find it looking on source code.

More details:

I can easily load user data into column with different name, but as soon as I am trying to load into column with same name as data field, expression cannot be used.

This problem is common for all data formats that has named fields - JSON and AVRO, and it affects loads from our proprietary formats. Data formats without field names - like CSV and Vertica Native Binary - do not have this problem.

(Do not mention Flex table - it is not relevant - user data is actually in proprietary data format with named data fields. JSON is used here to simplify case).

SruthiA

it is a known issue. we have a JIRA for it VER-28231. Please open a support case. we can check internally.