How to load JSON with expression on JSON field to same column name
I am loading through JSON.
To avoid complex time and date string formats, my JSON always contain int for date/time/timestamp.
For example, I am using number of micros since epoch for timestamp JSON fields, number of micros since midnight for time JSON fields, number of days from epoch for date fields.
Of course JSON fields has names, and users want to have column names same as field names.
Here is a problem - I found no way to convert number of micros since epoch into timestamp column in copy transform, without changing column name.
Yes I can declare my input field with micros since epoch as filler, and then load into column with different name using to_timestamp function. But column name became different.
For example, my JSON data has field ts with micros since epoch:
ts contains number of micros since epoch.
I can write in copy transform
ts filler int,
ts_ts as to_timestamp(ts)
That works, but column name changed - input data has field ts, table has column ts_ts. Users are not happy.
Question - how to load ts into table column name ts with type timestamp?
I cannot use date/time format in copy transform, first because there is no format for micros since epoch, second because formats are for strings and I have int.
Same problem when I have field in JSON as int 0 or 1, and users want to see column with same name but type bool.
"isB" : 1, - this is JSON for true
I cannot load it into table column named isB type boolean. I can load into table column named isB_renamed.
It appears, Vertica copy transform lack option to do expression on input column and same to table field with same name. Table field has to be different, to do expression on input field in transform.
In my case, I have to have table columns with different names comparing to JSON, in a lot of cases. Each time when I need to do any expression on input field I have ot create in table column with different name.
Problem is not limited to JSON data format. It is present for any format that has names for input data fields. For example problem exist for Vertica supported AVRO format.
I will appreciate if you give me some hint how to work around this problem.