Snowflake lateral flatten with changing keys in json

Question

I have a table in Snowflake with similar structure as below,

----------------------------------------
Name | Number
----------------------------------------
Dim_1 | {'Table_1': 100}
Dim_1 | {'Table_1': 101}
Dim_2 | {'Table_2': 200, 'Table_3': 300}
Dim_2 | {'Table_2': 201, 'Table_3': 301}

and I am trying to get output as :

-----------------------
Name | Table | Entries
-----------------------
Dim_1 | Table_1 | 100
Dim_1 | Table_1 | 101
Dim_2 | Table_2 | 200
Dim_2 | Table_3 | 300
Dim_2 | Table_2 | 201
Dim_2 | Table_3 | 301

Any ideas?

Hi, welcome to SO! You should consider showing us what you have done so far to try and solve the problem. https://stackoverflow.com/help/how-to-ask — thomas_f, Feb 13 '20 at 19:31

score 1 · Answer 1 · answered Feb 15 '20 at 18:21

A straightforward application of the FLATTEN function can work here. The function creates a lateral view with exploded rows from each source row. For flat objects, all key value pairs are exploded into one row each.

Using the KEY portion of the flattened result provides the Table part of the desired output, and the VALUE portion provides Entries. Since FLATTEN produces a lateral view, you still have access to the preceding source table row values to continue retaining the Name column in the result.

An example query that performs the transformation:

SELECT
  t.name AS "Name",
  lv.key AS "Table",
  lv.value AS "Entries"
FROM
  data_table t,
  LATERAL FLATTEN(input => t.number) lv

Snowflake lateral flatten with changing keys in json

1 Answers1