CODEWITHSUNDEEP
snowflake-cloud-data-platform
Karthik
Karthik

Reputation: 471

Snowflake: JSON Data in Array

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;

Upvotes: 3

Views: 1443

Answers (1)

Karthik
Karthik

Reputation: 471

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;

Upvotes: 3

Related Questions