How to parse a string with comma separated values in AWS IoT SQL?

Question

I am trying to parse a long string with comma-separated values such as "lat,long,distance,,elevation". String is actually quite long and I need to fetch each value and save the fetched values in different columns in dynamodb. I am using dyamodbv2 rule. Functions I found that could be useful were substring(String, Int [, Int]), length(String), indexof(String, String) and get().

For example I get data like this:

{
  LOCATION_DATA: "lat,long,distance,,elevation"
}

Here is what I have done so far,

//first value - 0 to next comma
substring(LOCATION_DATA, 0, indexof(LOCATION_DATA, ',')) as latitude,
//second value - substring starting from last substring to next comma
substring(substring(LOCATION_DATA, indexof(LOCATION_DATA, ',') +1 ) ,
              0,     
              indexof(substring(LOCATION_DATA, indexof(LOCATION_DATA, ',') +1 ), ',') 
) as longitude,
...

But this gets too verbose and moving to next comma-separated value increasingly difficult. Is there a way to convert comma-separated values to array and then fetch them with get(0), get(1).. ? I have to fetch around 20 fields this way!

Also, the values can be of varying length, and some fields can be empty, such as value between "distance,,elevation" in example strings. These empty values can be ignored.

As far as I now, there is no way I can store and create custom functions, or use any other function than provided in http://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-functions.html.

Answer 1

In rails, you can convert a string to array based on a separator Example

LOCATION_DATA = "lat,long,distance,,elevation"
myarray = LOCATION_DATA.split(',')

Then you can use

myarray[0]="lat"
myarray[1]="long"
myarray[2]="distance"
myarray[3]=""
myarray[4]="elevation"

You can also convert these strings to integer or float as:

myarray[0].to_i
myarray[2].to_f

Hope This Helps