Is there a way to extract data from a map(varchar, varchar) column in SQL?

user_13582876 0
Viewed 1868

The data is stored as map(varchar, varchar) and looks like this:

Date           Info                                                  ID
2020-06-10     {"Price":"102.45", "Time":"09:31", "Symbol":"AAPL"}   10
2020-06-10     {"Price":"10.28", "Time":"12:31", "Symbol":"MSFT"}    10
2020-06-11     {"Price":"12.45", "Time":"09:48", "Symbol":"T"}       10

Is there a way to split up the info column and return a table where each entry has its own column? Something like this:

Date          Price        Time       Symbol       ID
2020-06-10   102.45       09:31         AAPL       10
2020-06-10    10.28       12:31         MSFT       10

Note, there is the potential for the time column to not appear in every entry. For example, an entry can look like this:

Date           Info                                  ID
2020-06-10     {"Price":"10.28", "Symbol":"MSFT"}    10

In this case, I would like it to just fill it with a nan value

Thanks

presto sql
2 Answers

You can use the subscript operator ([]) or the element_at function to access the values in the map. The difference between the two is that [] will fail with an error if the key is missing from the map.

WITH data(dt, info, id) AS (VALUES
    (DATE '2020-06-10',  map_from_entries(ARRAY[('Price', '102.45'), ('Time', '09:31'), ('Symbol','AAPL')]), 10),
    (DATE '2020-06-10',  map_from_entries(ARRAY[('Price', '10.28'), ('Time', '12:31'), ('Symbol','MSFT')]), 10),
    (DATE '2020-06-11',  map_from_entries(ARRAY[('Price', '12.45'), ('Time', '09:48'), ('Symbol','T')]), 10),
    (DATE '2020-06-12',  map_from_entries(ARRAY[('Price', '20.99'), ('Symbol','X')]), 10))

SELECT 
   dt AS "date", 
   element_at(info, 'Price') AS price, 
   element_at(info, 'Time') AS time, 
   element_at(info, 'Symbol') AS symbol, 
   id
FROM data

    date    | price  | time  | symbol | id
------------+--------+-------+--------+----
 2020-06-10 | 102.45 | 09:31 | AAPL   | 10
 2020-06-10 | 10.28  | 12:31 | MSFT   | 10
 2020-06-11 | 12.45  | 09:48 | T      | 10
 2020-06-12 | 20.99  | NULL  | X      | 10
user_29587520

This answers the original version of the question.

If that is really a string, you can use regular expressions:

select t.*,
       regexp_extract(info, '"Price":"([^"]*)"', 1) as price,
       regexp_extract(info, '"Symbol":"([^"]*)"', 1) as symbol,
       regexp_extract(info, '"Time":"([^"]*)"', 1) as time
from t;
user_11440350
Related