Блог

У меня есть postgres DB, вызываемая sales с помощью json-объекта, data содержащего около 100 внешних ключей, давайте назовем их k1,k2,k3..,k100 .

Я хочу написать запрос

 select * from sales some_function(data)
  

который просто возвращает что-то вроде

 k1    |  k2 | .. | k100
--------------------
"foo" | "bar" | .. | 2
"fizz"| "buzz"| .. | 10
  

т.е. просто распакуйте ключи как columnn, а их значения — как row.
Обратите внимание, k1,k2..k100 что это не их настоящее имя, поэтому я не могу сделать

data->> key цикл

Это невозможно. Одним из ограничений языка SQL является то, что все столбцы (и их типы данных) должны быть известны базе данных при разборе инструкции — то есть до ее фактического запуска.

Вам придется писать каждый из них отдельно:

 select data ->> 'k1' as k1, data ->> 'k2' as k2, ...
from sales
  

Один из способов упростить это — динамически генерировать представление, извлекая все ключи JSON из столбца, а затем используя динамический SQL для создания представления. Однако вам нужно будет заново создавать это представление каждый раз, когда меняется количество ключей.

Что-то вроде (не проверено!)

 do
$$
declare
  l_columns text;
  l_sql text;
begin
  select string_agg(distinct format('data ->> %L as %I', t.key, t.key), ', ')
    into l_columns
  from sales s
    cross join jsonb_each(s.data) as t(key, value);

  -- l_columns now contains something like: 
  -- data ->> 'k1' as k1, data ->> 'k2' as k2

  -- now create a view from that
  l_sql := 'create view sales_keys as select '||l_columns||' from sales';
  execute l_sql;
end;
$$
;
  

Вероятно, вы захотите добавить, например, столбцы первичного ключа в представление, чтобы вы могли сопоставить значения JSON с исходными строками.