Search code examples
sqldata-sciencedata-analysisprestotrino

Extracting value from curly bracket SQL

I am trying figure out how to extract a value from curly brackets in a column in Prestosql.

The field looks like, 

rates
{"B":750}
{"B":1600}
{"B":900}

I want to extract the number values only in each bracket. Also, if I want to divide that by 10 and then divide by 20 would that be easy to add into the query?

The rates column is of type map(varchar, bigint).

Solution

  • Since rates column is of type map(varchar, bigint). You can use Presto Map Functions and Operators on it. Examples:

    SELECT rates['B'] FROM ... -- value under key "B"
SELECT map_values(rates) FROM ... -- all values in a map

    See more in the Presto documentation.