CODEWITHSUNDEEP
sqljsonsnowflake-cloud-data-platformflatten
user18466310
user18466310

Reputation: 81

How to flatten a json in snowflake? sql

I have a table "table_1" with one column called "Value" and it only has one entry. The entry in the column is a json that looks like

{
  "c1": "A",
  "c10": "B",
  "c100": "C",
  "c101": "D",
  "c102": "E",
  "c103": "F",
  "c104": "G",
.......
}

I would like to just separate this json into two columns, where one column contains the keys (c1, c10 etc), and the second columns contains the associated values for that key (A, B etc). Is there a way I can do this? There are about 125 keys in my json

Upvotes: 0

Views: 281

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

It is possible to achieve it using FLATTEN function:

CREATE OR REPLACE TABLE tab
AS
SELECT PARSE_JSON('{
  "c1": "A",
  "c10": "B",
  "c100": "C",
  "c101": "D",
  "c102": "E",
  "c103": "F",
  "c104": "G",
}') AS col;

SELECT KEY, VALUE::TEXT AS value
FROM tab
,TABLE(FLATTEN (INPUT => tab.COL));

Output:

enter image description here

Upvotes: 1

Related Questions