CODEWITHSUNDEEP
snowflake-cloud-data-platform
i_am_cris
i_am_cris

Reputation: 627

Querying json that starts with an array

I have a JSON that starts with an array and I don't manage to query it. JSON is in this format:

[
{"@id":1,
"field1":"qwerty",
"@field2":{"name":"my_name", "name2":"my_name_2"},
"field3":{"event":[{"event_type":"OP",...}]}
},
{"@id":2..
}
]

Any suggestions on how to query this?

If I try to use lateral flatten I don't know what key to use:

select 
'???'.Value:@id::string as id
from tabl1
,lateral flatten (tabl1_GB_RECORD:???) as gb_record

Upvotes: 0

Views: 59

Answers (2)

Mike Walton
Mike Walton

Reputation: 7339

You leverage the field that you want to flatten when the json begins with an array. Something along these lines:

WITH x AS (
SELECT parse_json('[
{"@id":1,
"field1":"qwerty",
"@field2":{"name":"my_name", "name2":"my_name_2"},
"field3":{"event":[{"event_type":"OP"}]}
},
{"@id":2,
"field1":"qwerty",
"@field2":{"name":"my_name", "name2":"my_name_2"},
"field3":{"event":[{"event_type":"OP"}]}
}
]') as json_data
)
SELECT y.value,
       y.value:"@id"::number as id,
       y.value:field1::string as field1,
       y.value:"@field2"::variant as field2,
       y.value:field3::variant as field3,
       y.value:"@field2":name::varchar as name
FROM x,
LATERAL FLATTEN (input=>json_data) y;

Upvotes: 1

demircioglu
demircioglu

Reputation: 3465

Your SQL was close but not complete, the following will give you @id values

with tbl1 (v) as (
 select parse_json('  
 [
 {"@id":1,
 "field1":"qwerty",
 "@field2":{"name":"my_name", "name2":"my_name_2"},
 "field3":{"event":[{"event_type":"OP"}]}
 },
 {"@id":2
 }
 ]')
)  
select t.value:"@id" id from tbl1
, lateral flatten (input => v)  as t

Result:

id
___
1
2

Let me know if you have any other questions

Upvotes: 1

Related Questions