Snowflake (stored procedure): flatten json object in table into several columns

Question

I have one table in snowflake with a column which contains a json blob. I want to flatten that column with a stored procedure. The issue is that I'd like to use the same stored procedure for different tables with different json schemas. In the example below the json consists of two key/value pairs. For another table there could be 5 key/value pairs which need to flatten.

Is it possible to do this with one stored procedure? If yes, how can I do that?

Original table

Animal	Name	Details (json blob)
Lion	Georg	lion key1: value1, lion key2: value2
Lion	Patrick	lion key1: value1, lion key2: value2

New table: Lion table

Name	lion key1	lion key2
Georg	value1	value2
Patrick	value1	value2
Paul	value1	value2

Note the previous question with a script to setup the data for further development stackoverflow.com/a/73200253/132438 — Felipe Hoffa
– Felipe Hoffa, Commented Aug 2, 2022 at 3:37

Gokhan Atil · Accepted Answer · 2022-08-02 09:59:40Z

0

I don't think it's feasible but here is a sample procedure:

CREATE OR REPLACE PROCEDURE generate_dynamic_table()
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
   c1 CURSOR FOR select DISTINCT KEY from mytable, lateral flatten( details );
   SQLstatement VARCHAR := 'CREATE TABLE targettable ( Name, ';
   SELstmt VARCHAR := 'AS SELECT Name, '; 
BEGIN
   FOR c IN c1 DO
      SQLstatement := SQLstatement || '"' || c.KEY || '", '; 
      SELstmt := SELstmt || 'details:"' || c.KEY || '", ';
   END FOR;
   SQLstatement := LEFT( SQLstatement, LEN(SQLstatement) - 2 ) || ') ';
   SELstmt := LEFT( SELstmt, LEN(SELstmt) - 2 ) || '';
   SQLstatement := SQLstatement || SELstmt || ' FROM mytable';
   EXECUTE IMMEDIATE SQLstatement;
   RETURN 'OK';
END;

call generate_dynamic_table();

After running the procedure:

select * from targettable;

+---------+-----------+-----------+
|  NAME   | lion key1 | lion key2 |
+---------+-----------+-----------+
| Georg   | "value1"  | "value2"  |
| Patrick | "value1"  | "value2"  |
+---------+-----------+-----------+

answered Aug 2, 2022 at 9:59

Gokhan Atil

10.6k2 gold badges13 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

siva Over a year ago

What do you mean by it's not feasible? What's the limitation of your proposed solution?

Gokhan Atil Over a year ago

@siva I meant generating auto tables from a table. There is no limitation of SP., but if the table is not simple as you shared, or JSON is more complex, the SP would be very complex and open to errors.

Collectives™ on Stack Overflow

Snowflake (stored procedure): flatten json object in table into several columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related