sql - 是否可以生成动态 sql 以返回雪花中具有各种列的行

Question

我们将各种数据存储为 JSON 列中的值/对。所有行的对名称都不相同，并且取决于某些元数据。

有没有一种方法可以编写 SQL 语句，根据某些元数据检索其中一些值对？

像动态生成的东西

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

我知道我可以在存储过程中创建和执行动态 SQL，但此 SQL 仅限于返回标量值...我们需要一个记录集。

Answer 1

鉴于此输入:

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" } ')
;

此查询将生成动态 SQL:

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

生成的 SQL 及其输出:

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"      

此存储过程将执行动态 SQL 而无需剪切和粘贴:

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'
$$
;

然后您可以调用存储过程并收集结果:

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

您提到根据某些元数据限制输出。您可以在动态 SQL 中执行此操作，例如通过过滤不同的字段列表。

感谢 davidgarrison 的 result_scan() 技术!

希望对您有所帮助。