So I have a column in a Snowflake table that stores JSON data but the column is of a varchar data type.

The JSON looks like this:

{   
    "FLAGS": [],   
    "BANNERS": {},   
    "TOOLS": {     
            "game.appConfig": {       
              "type": [         
                "small",       
                 "normal",        
                  "huge"
              ],      
              "flow": [         
                "control",       
                "noncontrol"
            ]   
        }  
    },   
    "PLATFORM": {} 
}

I want to filter only the data inside TOOLS and want to get the following result:

How can I achieve this?

CodePudding user response：

I assumed that the TOOLs can have more than one tool ID, so I wrote this query:

with mydata as ( select
'{
    "FLAGS": [],   
    "BANNERS": {},   
    "TOOLS": {     
            "game.appConfig": {       
              "type": [         
                "small",       
                 "normal",        
                  "huge"
              ],      
              "flow": [         
                "control",       
                "noncontrol"
            ]   
        }  
    },   
    "PLATFORM": {} 
}' as v1 )
select main.KEY TOOLS_ID, sub.KEY TOOLS
from mydata,
lateral flatten ( parse_json(v1):"TOOLS" ) main,
lateral flatten ( main.VALUE ) sub;

 ---------------- ------- 
|    TOOLS_ID    | TOOLS |
 ---------------- ------- 
| game.appConfig | flow  |
| game.appConfig | type  |
 ---------------- ------- 

CodePudding user response：

Assuming the column name is C1 and table name T1:

select a.t:"TOOLS":"game.appConfig"::string from (select 
parse_json(to_variant(C1))t from T1) a