Extract key Value pair from a column value which is a Json structure

0

Hi All,
Your help on this will be appreciated.

I have a table like

3 columns -> _ID || Col_A || Col_B

Col_A values is as :
"{
""FIELDS"" : [ ""Fruits"" ],
""MAP"" : {
""101"" : ""APPLE"",
""102"" : ""BANANA"",
""103"" : ""ORANGE"",
""104"" : ""PINEAPPLE"",
""105"" : ""APRICOTS"",
""VOG"" : ""COCONUT"",
""VOT"" : ""BERRY""
},
""TYPE"" : ""MAP"",
""updatedAt"" : ""Wed Sep 11 10:12:11 UTC 2019"",
""updatedBy"" : ""Mark""
}"

I want to extract the values from Col_A as
ID || Name

101 || APPLE
102 || BANANA
103 || ORANGE
104 || PINEAPPLE
105 || APRICOTS
VOG || COCONUT
VOT || BERRY

已提问 4 年前1667 查看次数
1 回答
0

got the solution to this.

  1. inner query to extract data from Jason structure.. specific array

  2. 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)

已回答 4 年前

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则