Converting Json column to tabular structure

user_12732153 0
Viewed 82

I have a table with column. one column is json - "session". The json is not nested. If I want to make the key of the json as new column name, how can I do that easily? The json is big, so is there something dynamic? without specifying each key in the json? I found only solution that return me key-value pairs but I want it on more relational style.

example:


User_name | Is active | Session
Kate          yes        {"session_id": 34386384,
                           "platform: Jha,
                            "last_login": 12/2/97}

I want it to be:

User_name | Is active | session_id | platform | last_login
Kate          yes        34386384     Jha         12/2/97

what i tried:

with cte as (
             select 
            from user_sessions
            ,lateral flatten(input => Session)
            )
select *
from cte
snowflake-cloud-data-platform
2 Answers
user_123967700

A simple accessing of the JSON can be done (if it is already been PARSE_JSON'ed)

the data CTE is just there for some input data. I also convert the date to a internal DATE object.

WITH data(user_name,is_active, session) as (
    SELECT 'Kate', true, PARSE_JSON('{"session_id": 34386384,"platform": "Jha", "last_login": "12/2/97"}')
)
SELECT 
    d.user_name,
    d.is_active,
    d.session:session_id::number as session_id,
    d.session:platform::text as platform,
    to_date(d.session:last_login::text, 'DD/MM/YY') as last_login 
FROM data AS d;

gives:

USER_NAME IS_ACTIVE SESSION_ID PLATFORM LAST_LOGIN
Kate TRUE 34386384 Jha 1997-02-12
user_439920
Related