Question 109570 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JSON from snowflake

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

Tags:

2

Answers


  1. 0

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

    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