CODEWITHSUNDEEP
sqljsonsnowflake-cloud-data-platform
OMG
OMG

Reputation: 263

Parsing JSON file in Snowflake Database

enter image description hereDatabase :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.

Upvotes: 1

Views: 2506

Answers (1)

Marcin Zukowski
Marcin Zukowski

Reputation: 4729

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

Upvotes: 2

Related Questions