Snowflake: JSON Data in Array

Question

JSON data as below

{name : Mike, job : [{name: abc, value: 123},{name: def,value: 456}]}

How to retrieve the value of name = abc and def?

EDIT:(SOLUTION) Got the solution myself thanks

 WITH x AS (
SELECT parse_json('{"name" : "Mike", "job" : [{"name": "abc", "value": "123"},{"name": "def","value": "456"}]}' ) as payload_json)
    select x.payload_json:name,
           job.value:name::varchar as name,
           job.value:value::varchar as value
     from x,
    lateral flatten( input => x.payload_json:job, outer => true) as job;

Karthik · Accepted Answer · 2021-08-10 18:41:48Z

3

I got the answer myself as below

 WITH x AS (
SELECT parse_json('{"name" : "Mike", "job" : [{"name": "abc", "value": "123"},{"name": "def","value": "456"}]}' ) as payload_json)
    select x.payload_json:name,
           job.value:name::varchar as name,
           job.value:value::varchar as value
     from x,
    lateral flatten( input => x.payload_json:job, outer => true) as job;

answered Aug 10, 2021 at 18:41

Karthik

4913 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simon D Over a year ago

Feel free to tag your own answer as the correct one!

Collectives™ on Stack Overflow

Snowflake: JSON Data in Array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related