CODEWITHSUNDEEP
snowflake-cloud-data-platform
Grammilo
Grammilo

Reputation: 1379

How to parse a specific data from a JSON string in SnowFlake?

I am very new to SnowFlake and I am trying to work on a dataset. The column I am interested in has multiple feedbacks combined into one in the JSON format and I want to dig only the relevant key. Here's the snapshot of lets say Column_X: enter image description here

Looking for a way to parse this data in such a way that I have a new column like "riskIndicator" and "riskIndicator" with values 27, 74 as two new rows. I am attempting to parse like the code below but that's not working. Had a look at the javascript/UDF approach but looks complicated for this piece.

,get_path(parse_json("riskIndicatorLNInstantID"),'riskCode') as riskIndicator

I will be thankful for any kind of help/suggestion here. Thank you.

Upvotes: 0

Views: 629

Answers (2)

FKayani
FKayani

Reputation: 1021

Lateral flatten can help extract the fields of a JSON object and is a very good alternative to extracting them one by one using the respective names. However, sometimes the JSON object can be nested and normally extracting those nested objects requires knowing their names

Docs Reference: https://community.snowflake.com/s/article/Dynamically-extract-multi-level-JSON-object-using-lateral-flatten

Upvotes: 0

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

So if the problem you are having is breaking up the json, you will want to use FLATTEN

with data as (
    select parse_json('[{"description":"unable to paste json", "riskCode":"27","seq":1},{"description":"typing in json is painful", "riskCode":"74","seq":2}]') as json
)
select d.json
    ,f.value:riskCode as riskIndicator
from data d
 ,lateral flatten(input=>d.json) f;

gives:

JSON                                       RISKINDICATOR
[{ "description": "unable to paste j...    "27"
[{ "description": "unable to paste j...    "74"

Upvotes: 1

Related Questions