将来自瓦尔查尔阵列athena的Json压扁

Question

试图通过values is in string datatype in the table用json值分解数组

id   values
1   [{"entity_id":9222.0,"entity_name":"A","position":1.0,"entity_price":133.23,"entity_discounted_price":285.0},{"entity_id":135455.0,"entity_name":"B","position":2.0,"entity_price":285.25},{"entity_id":9207.0,"entity_name":"C","position":3.0,"entity_price":55.0}]
2   [{"entity_id":9231.0,"entity_name":"D","position":1.0,"entity_price":130.30}]

预期结果

id entity_id entity_name position entity_price entity_discounted_price
1  9222      A           1        133.23       285.0
1  135455    B           2        285.25       null
1  9207      C           3        55.0         null
2  9231      D           1        130.30       null

我尝试过的

select a.*
,sites.entity_id
,sites.entity_name
,sites.position
,sites.entity_price
,sites.entity_discounted_price 
from (select * from table1) a , unnest(cast(values as array(varchar))) as t(sites)

以上代码抛出错误Cannot cast varchar to array(varchar)

Answer 1

不能将varchar强制转换为array(varchar)。在这种特殊情况下，您可以使用json_parse并处理json，例如通过转换为array(map(varchar, json)):

-- sample data
WITH dataset(id, "values") AS (
VALUES (1, '[{"entity_id":9222.0,"entity_name":"A","position":1.0,"entity_price":133.23,"entity_discounted_price":285.0},{"entity_id":135455.0,"entity_name":"B","position":2.0,"entity_price":285.25},{"entity_id":9207.0,"entity_name":"C","position":3.0,"entity_price":55.0}]'),
(2, '[{"entity_id":9231.0,"entity_name":"D","position":1.0,"entity_price":130.30}]')
)
-- query
select id
, sites['entity_id']
, sites['entity_name']
, sites['position']
, sites['entity_price']
, try(sites['entity_discounted_price'])
from dataset,
unnest(cast(json_parse("values") as array(map(varchar, json)))) as t(sites);

输出：

>_col4<1.0>133.23<285.0><2.0>空[/tr>空空

id	_col1	_col2	_col3	-col5
1	9222.0	A
1	135455.0	B	285.25
1	9207.0	C	3.0	55.0
2	9231.0	D	1.0	130.3