CODEWITHSUNDEEP
jsonsnowflake-cloud-data-platform
Roman
Roman

Reputation: 577

select key-value pairs from json as rows in Snowflake

Assume I have the following JSON:

{
  "CURRENCY_CODE": "INR",
  "CURRENCY_NAME": "Indian Rupee",
  "ID_CURRENCY": 8,
  "ISO_CODE": 4217
}

I want to query it in Snowflake so I get the following output:

Key Value
CURRENCY_CODE INR
CURRENCY_NAME Indian Rupee
ID_CURRENCY 8
ISO_CODE 4217

I expect something like:

select d.key, d.value
from table('Here goes json') d

Does Snowflake have any function to achieve this?

Upvotes: 2

Views: 1795

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

It is possible to pass the argument directly into FLATTEN:

SELECT f.*
FROM TABLE(FLATTEN(INPUT => PARSE_JSON('<here goes json>'))) f;

Upvotes: 2

JNevill
JNevill

Reputation: 50273

You can use LATERAL FLATTEN() in your FROM clause to achieve this.

As an example:

SELECT fdt.KEY, fdt.VALUE
FROM VALUES('{
  "CURRENCY_CODE": "INR",
  "CURRENCY_NAME": "Indian Rupee",
  "ID_CURRENCY": 8,
  "ISO_CODE": 4217
}') dt
,lateral flatten( input => parse_json(column1) ) fdt;
KEY VALUE
CURRENCY_CODE INR
CURRENCY_NAME Indian Rupee
ID_CURRENCY 8
ISO_CODE 4217

Upvotes: 2

Related Questions