skip to Main Content

I’m working with Snowflake.

I’m executing these statements:

create table test(
src varchar
);

insert into test
values ('{"value": 
     {"evaluation_forms": 
      [ {"evaluations": 
       [ {"channel_meta": 
          {"after_call_work_time": [], 
           "agent_first_name": ["KATRINA"],
           "agent_hung_up": [],
           "agent_last_name": ["COX"],
           "agent_unique_id": ["LO_00130604"], 
           "agent_username": [], 
           "alternate_call_id": [], 
           "total_time": []             
          }
         
        } ]
    } ]
}
}'
);

issuing this statement

SELECT
  cm.*
  FROM
    (select parse_json(src) src from test) t
    , LATERAL FLATTEN(INPUT => SRC:value) v
    , LATERAL FLATTEN(INPUT => v.value) vv
    , LATERAL FLATTEN(INPUT => vv.value) ev
    , LATERAL FLATTEN(INPUT => ev.value) cm

gets stuff, including the json in the value column.

issuing this statement

SELECT
    cm.channel_meta.agent_first_name[0],
  cm.*
  FROM
    (select parse_json(src) src from test) t
    , LATERAL FLATTEN(INPUT => SRC:value) v
    , LATERAL FLATTEN(INPUT => v.value) vv
    , LATERAL FLATTEN(INPUT => vv.value) ev
    , LATERAL FLATTEN(INPUT => ev.value) cm

gets me an invalid identifier error.

All sorts of varieties gets me an invalid identifier error.

How do I extract the agent_first_name from this json?

Thanks, –sw

2

Answers


  1. You have to specify ‘.value’ in the select clause

       SELECT
        cm.value:channel_meta.agent_first_name[0],
        cm.*
        FROM
        (select parse_json(src) src from test) t
        , LATERAL FLATTEN(INPUT => SRC:value) v
        , LATERAL FLATTEN(INPUT => v.value) vv
        , LATERAL FLATTEN(INPUT => vv.value) ev
        , LATERAL FLATTEN(INPUT => ev.value) cm
    
    Login or Signup to reply.
  2. If there are more than one value in the agent_first_name array and you want to display all the values in this array, then specify as cm.value:channel_meta.agent_first_name ( without the array position [0] ).

    If you are looking for the other elements like agent_username, agent_last_name etc, you can specify select clause as cm.value:channel_meta.agent_username, cm.value:channel_meta.agent_last_name

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search