使用数据示例进行测试。将cte替换为您的表。阅读代码中的注解：

with your_table as (--use your table instead of this CTE
select stack(2,
             "monday",'[{"temp" : 45, "weather": "rainny"}, {"temp" : 25, "weather": "sunny"}, {"temp" : 15, "weather": "storm"}]',
             "tuesday" ,'[{"temp" : 5, "weather": "winter"}, {"temp" : 10, "weather": "sun"}, {"temp" : 18, "weather": "dawn"}]'
            )as (day, type_of_day)
) --use your table instead of this CTE

select s.day, array(get_json_object(type_of_day_array[0],'$.temp'),
                    get_json_object(type_of_day_array[1],'$.temp'),
                    get_json_object(type_of_day_array[2],'$.temp')
                   ) as result_array --extract JSON elements and construct array
 from
(
select day,  split(regexp_replace(regexp_replace(type_of_day,'\\[|\\]',''),   --remove square brackets
                                  '\\}, *\\{','\\}##\\{'),                    --make convenient split separator
                   '##')                                                      --split                                  
          as type_of_day_array
  from your_table                                                             --use your table instead of this CTE
)s;

结果：

s.day   result_array    

monday  ["45","25","15"]    
tuesday ["5","10","18"]

如果json数组可以包含三个以上的元素，那么可以使用横向视图explode或posexplode，然后构建结果数组，如下所示：https://stackoverflow.com/a/51570035/2700344.
在cast（）中 Package 数组元素。。。如果你需要的话 array<int> 结果不是 array<string> :

cast(get_json_object(type_of_day[0],'$.temp') as int)...