Trying to explode array with json values though values is in string datatype in the table

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}]

Expected Result

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

What I have tried

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)

above code is throwing error Cannot cast varchar to array(varchar)

CodePudding user response：

You can't cast varchar to array(varchar). In this particular case you can use json_parse and process the json, for example by casting into 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);

Output: