Parsing JSON file in Snowflake Database

Question

Database :SNOWFLAKE

My table contains JSON data for example:

{
    "bucket":"IN_Apps",
    "bySeqno":56,
    "cas":1527639206906626048,
    "content":"eyJoaWdoQmluIjoiNTQ4NTA4MDkiLCJkb2N1bWVudFR5cGUiOiJJSU5ETyIsImNhcmRUeXBlIyayI6Ik1BU1RFUkNBUkQifQ==",
    "event":"mutation",
    "expiration":0,
    "flags":33554432,
    "key":"iin54850809",
    "lockTime":0,
    "partition":948,
    "revSeqno":1,
    "vBucketUuid":137987627737694
}

when i tried to parse it.

select 
parse_json:bucket::string as bucket ,
parse_json:bySeqno::string as bySeqno ,
parse_json:cas::INT as cas ,
parse_json:content::string as content ,
parse_json:event::string as event 
,parse_json:expiration::string as expiration
,parse_json:flags::string as flags
,parse_json:key::string as key
,parse_json:lockTime::string as lockTime
,parse_json:partition::string as partition
,parse_json:revSeqno::string as revSeqno
,parse_json:vBucketUuid::string as vBucketUuid
from STG_YS_APPS v

but it is throwing error like.

SQL compilation error: error line 2 at position 0 invalid identifier > >'PARSE_JSON'

may someone please help me.

What is the exact schema of your table? Do you have there a column name called "parse_json" ? That's what your SQL suggests, but that would be weird. Maybe do "DESC TABLE v" and add the output to the question — Marcin Zukowski
@MarcinZukowski: No i don't have column named as parse_json. it is inbuilt in snowflake for parsing JSON. — OMG
Well, your query is written like you did. Please run "DESC TABLE v" and provide output — Marcin Zukowski

Marcin Zukowski Marcin Zukowski · Accepted Answer · 2018-11-14T05:12:57

Answer with a known schema

Update: Since you provided schema, which shows a VAR column of VARIANT type, here's what you need, couldn't be simpler:

select
var:bucket::string as bucket,
var:bySeqno::string as bySeqno,
var:cas::int as cas
...
from STG_YS_APPS v

Below the answer before the schema was known

I'll assume you have a VARCHAR (or similar) column in your table that is called json, and stores the values you presented. You didn't provide the schema, so please adjust the column name as necessary.

You're not using PARSE_JSON as a function in your SQL. You should write something like

select
parse_json(json):bucket::string as bucket,
parse_json(json):bySeqno::string as bySeqno,
parse_json(json):cas::int as cas
...
from STG_YS_APPS v

Parsing JSON file in Snowflake Database

1 Answers