Datasource > Query: Custom SQL Query with virtual columns - possible

[Vukasin Nikodijevic]

I have pretty non standard requirement - at least I think it is as I just recently started with Spotfire.

 

Data from AWS Athena ARRAY field which contains objects are transformed/flattened to tabular.

Tabular structure follows structure of object inside of ARRAY.

Transformation part works just great.

 

New tabular columns are different than original from AWS Athena table.

Is there any way to add custom columns or virtual columns for purpose of mapping transformed data columns into resultset for visualization purposes

 

AWS Table:

| col 1 | results | col 3 |

|array | array | array |

Result object is JSON and is structured as:

{

reading_1: number

reading_2: number

reading_3: number

}

Transformation will flatten `result` objects into tabular:

| reading_1 | reading_2 | reading_3 |

| double | double | double |

| double | double | double |

.

.

.

| double | double | double |

 

 

Problem:

Columns reading_1, 2, 3 aren't available in original AWS Athena table and I need somehow to map them to some kind of virtual columns.

Is it possible