Можно ли сгенерировать динамический 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 ()!

Надеюсь, это поможет.

Если у вас уже есть готовый динамический sql, есть способы получить наборы результатов из хранимой процедуры, кроме скалярных значений.

Вы можете просмотреть несколько вариантов и примеров в документации SnowFlake:

• Возврат набора результатов
• Использование RESULT_SCAN для получения результата из хранимой процедуры

Я бы использовал для этого javascript UDTF. Он обеспечивает ту же мощность, что и хранимая процедура, но с табличным выводом.

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

Может быть, я слишком упрощаю здесь, но не могли бы вы просто ... сослаться на столбцы JSON?
Нет необходимости в хранимых процедурах или UDTF.

Если один объект / строка не имеет определенного атрибута, его значение будет NULL, и его можно будет проверить.

Если вы хотите преобразовать объекты и массивы нетривиальными способами, используйте JavaScript, но чтобы получить простые атрибуты, просто сделайте это на SQL.

Если вы хотите вернуть разные столбцы (разные имена), это невозможно, за исключением возврата типа данных VARIANT (например, вашего ввода) с различными атрибутами.

[Изменить: динамическая комбинация SQL / result_scan davidgarrison / waldente - это хорошо, но ее все еще трудно использовать из внешнего инструмента с ограниченными способами выполнения сценариев SQL, даже если сценарий состоит всего из двух запросов]