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

Question

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

Answer

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))) ,  
  (map_values(CAST(CAST(Program_jason AS JSON) AS map)))   
)  
AS m(key,value)

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

I have a table like

3 columns -> _ID || Col_A || Col_B

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

相關內容

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