How to flatten a json in snowflake? sql-CodePudding

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

CodePudding user response：

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: