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

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

有没有办法编写一个 SQL 语句来根据某些元数据检索其中一些值对？

类似动态生成的东西

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

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

鉴于此输入：

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() 技术！

希望这有帮助。