CODEWITHSUNDEEP
jsonsnowflake-cloud-data-platform
William Gao
William Gao

Reputation: 11

Extract JSON Values in Snowflake SQL

I want to create two columns from a column of values containing JSON in Snowflake using SQL.

Say this table is called keywords_bids then there is a column called keywords that has JSON in it
example json in a cell in the keywords column:
row1: {"apple":0.1, "peach":0.2, "banana":0.1} row2: similar JSON, etc....
input image I want to create a columns called keyword and it is bid price from the JSON output would be:
keyword | Bid
'apple' | 0.1
'peach' | 0.2
'banana'| 0.3

Upvotes: 1

Views: 10907

Answers (3)

Ismail Sajjad
Ismail Sajjad

Reputation: 61

Easy way to get values from JSON in snowflake

For example:
Table: ABC Column: Json_column it contain JSON content like

 "carriers": [
        {
          "code": "AAAA",
          "flightNumber": "XXXX",
          "type": "OPERATING"
        },
        {
          "code": "BBBB",
          "flightNumber": "YYYY",
          "type": "MARKETING"
        }
      ]

SELECT Json_column:carriers[0].code::varchar AS first_child,
Json_column:carriers[1].code::varchar AS first_childFROM ABC;

Result of SQL will be

AAAA, BBBB

As data is in array we are using [0] and [1] getting the value of JSON.

Upvotes: 0

FKayani
FKayani

Reputation: 1021

https://community.snowflake.com/s/article/Dynamically-extracting-JSON-using-LATERAL-FLATTEN

This article is to demonstrate various examples of using LATERAL FLATTEN to extract information from a JSON Document. Examples are provided for its utilization together with GET_PATH, UNPIVOT, and SEQ functions.

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

First for JSON you'll need to change the single quotes to double quotes.

Then you just need to flatten the json to get keys and values:

with data as (
select parse_json('{"apple":0.1, "peach":0.2, "banana":0.1}') j
)

select k.key, k.value
from data, table(flatten(j)) k
;

enter image description here

Upvotes: 1

Related Questions