CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Eric Mamet
Eric Mamet

Reputation: 3671

Is it possible to generate dynamic sql to return rows with various columns in snowflake

We store various data as value/pairs within a JSON column. The pair names are not the same for all rows and depend on some metadata.

Is there a way I could write a SQL statement that retrieves some of these value pairs depending on some metadata?

Something like a dynamically generated 

SELECT MyJson:FruitShape, MyJson:Fruitsize FROM MyTable WHERE ...

I understand I can create and execute dynamic SQL within a stored procedure but this SQL is limited to returning a scalar vale... We need a recordset.

Upvotes: 2

Views: 2112

Answers (4)

Hans Henrik Eriksen
Hans Henrik Eriksen

Reputation: 2870

Maybe I'm over simplifying things here, but can't you just ... reference the JSON columns?
No need for Stored Procedures or UDTFs.

If one object/row doesn't have a particular attribute, its value will be NULL and can be tested for.

If you want to transform objects and arrays in non-trivial ways, JavaScript is the way to go, but to get simple attributes, just do it in SQL.

If you want to return different columns (different names), that's impossible except for returning a VARIANT data type (ie like your input) with varying attributes.

[Edit: davidgarrison/waldente's dynamic SQL/result_scan combo is nice, but may still be hard to utilize from an external tool with limited ways of executing SQL scripts, even when the script is consisting of just two queries]

Upvotes: 0

waldente
waldente

Reputation: 1434

Given this input:

create or replace table t as
select parse_json($1) my_json
from values
('{ "FruitShape":"Round", "FruitSize":55 } '),
('{ "FruitShape":"Square"  } '),
('{ "FruitShape":"Oblong", "FruitSize":22, "FruitColor":"Chartreuse" } ')
;

This query will generate the dynanmic SQL:

select 'select ' 
  || (select listagg(distinct 'my_json:'||key::text, ',') from t, lateral flatten(input=>t.my_json, mode=>'OBJECT')) 
  || ' from t;';

Generated SQL and it's output:

select my_json:FruitShape, my_json:FruitSize, my_json:FruitColor from t;

MY_JSON:FRUITSHAPE | MY_JSON:FRUITSIZE | MY_JSON:FRUITCOLOR
-------------------+-------------------+-------------------
"Round"            | 55                | NULL              
"Square"           | NULL              | NULL              
"Oblong"           | 22                | "Chartreuse"

This stored procedure will execute dynamic SQL without cut-and-paste:

create or replace procedure p()
returns string
language javascript
strict
execute as caller
as
$$
  const statement1 =  `
    select 'select ' 
      || (select listagg(distinct 'my_json:'||key::text, ', ') from t, lateral flatten(input=>t.my_json, mode=>'OBJECT')) 
      || ' from t'
  `
  const rs1 = snowflake.execute ({sqlText: statement1})
  rs1.next()
  const statement2 = rs1.getColumnValue(1)
  const rs2 = snowflake.execute ({sqlText: statement2})
  return 'SUCCESS'
$$
;

Then you can call the stored procedure and collect the results:

call p();
select * from table(result_scan(-2))

You mentioned constraining the output depending on some metadata. You can do that in the dynamic SQL, for example by filtering the distinct list of fields.

Credits to davidgarrison for the result_scan() technique!

Hope that's helpful.

Upvotes: 4

David Garrison
David Garrison

Reputation: 2880

If you already have the dynamic sql ready, there are ways to get result sets out of a Stored Procedure other than just scalar values.

You can look through a few options, and examples in SnowFlake's documentation:

Upvotes: 3

Mike Walton
Mike Walton

Reputation: 7369

I would leverage a javascript UDTF for this. It provides the same power as a stored procedure, but with a table output.

https://docs.snowflake.net/manuals/sql-reference/udf-js-table-functions.html

Upvotes: 0

Related Questions