1개 답변
- 최신
- 최다 투표
- 가장 많은 댓글
0
got the solution to this.
-
inner query to extract data from Jason structure.. specific array
-
un nesting both the keys and value pair form the structure. check the syntax
SELECT
DISTINCT m.key AS PROGRAMID,TRIM(UPPER(CAST(m.value AS VARCHAR)))AS PROGRAM
from
(SELECT json_extract(programidudf, '$.MAP') as Program_jason
FROM "tableaureports"."global_udf_parquet" where programidudf is not null)
CROSS JOIN
UNNEST
(
(map_keys(CAST(CAST(Program_jason AS JSON) AS map<varchar,json>))) ,
(map_values(CAST(CAST(Program_jason AS JSON) AS map<varchar,json>)))
)
AS m(key,value)
답변함 5년 전