有没有一种方法可以从SQL中的映射(varchar，varchar)列中提取数据

Question

数据存储为map(varchar，varchar(，如下所示：

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

有没有一种方法可以拆分信息列并返回一个表，其中每个条目都有自己的列？类似这样的东西：

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

请注意，时间列可能不会出现在每个条目中。例如，一个条目可以如下所示：

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

在这种情况下，我希望它只填充一个nan值

感谢

Answer 1

您可以使用下标运算符([](或element_at函数来访问映射中的值。两者的区别在于，如果映射中缺少密钥，[]将失败并出现错误。

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

Answer 2

这回答了问题的原始版本。

如果这真的是一个字符串，你可以使用正则表达式：

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;