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

asked 3 years ago114 views
1 Answer
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)

answered 3 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions