Posts Tagged ‘map’

Presto supports multiple Array and JSON functions using which you can write queries to get the required results. There is no single way to define a CREATE TABLE and later your QUERIES. You can have CREATE TABLE predefine the schema structure and later your queries can refer the elements you need in the schema. Or you can define your schema as a string and later use functions on your queries to parse this string to get the required results.

Now, I was able to use the following CREATE TABLE syntax on the DynamoDB items having List of strings. I was able to flatten out the list of strings using some of the functions like CAST. Please note that this is not the only way to define table and query. My query might be over complexing what your are trying to get. There might be a simpler way as well. So, its really important that you understand the data types that you define and what each query returns and use the correct functions as each function takes a datatype and returns another datatype.

# damageShapes can take different structures in schema ex : damageShapes struct<L:array<struct<S:string>>> or damageShapes map<string,array<map<string,string>>> or struct<L:array<map<string,string>>> .

# Or if may be a simple string so that you need to rely on queries to parse this string.

# Since damageshapes.l is array(row(s varchar)) , you can find any presto functions which can flatten this to your required format which is array<varchar>. If there is not direct function, you might need to do 2 conversions.

# values_of_damageshapes is in JSON format and you can use JSON functions to get the necessary output.

# https://prestodb.io/docs/current/functions/json.html

-------------------------------------------------

# Here I am converting values_of_damageshapes(json) to ARRAY(ARRAY(varchar)))) so that I use an array function called flatten which Flattens an array(array(T)) to an array(T) by concatenating the contained arrays.