(db) PostgreSQL json / jsonb column to View + Hasura lifehack

PostgreSQL – потрясающая База Данных (БД). Одной из ее самых крутых функция является полноценная поддержка типа json и даже свой более оптимизированный jsonb. Фильрации, сортировки, поиск по ключам, совпадениям и векторам, даже функции и триггеры, все есть из коробки.

Но бывают ситуации, когда вам хочется хранить данные в jsonb, а нужна таблица с этими данными.

Например, такая ситуация происходит, если использовать Hasura.

Hasura – это Access Managment и GUI до вашей PostgreSQL, которая при этом еще автоматический создает GraphQL CRUD API поверх всех таблиц. Hasura нафарширована таким огромным и потрясающим функционал, что я буду ее очень часто упомянать и рассказывать про ее прелести.

Так вот, Hasura дает CRUD API, который позволяет делать поиск по всем колонкам таблиц, в том числе jsonb. Но на данный момент (июль 2020) у поиска по jsonb отсутсвует много важных функций, которые дает PostgreSQL.

Чтобы врубить все возможности Hasura на полную надо превратить колонку jsonb в Представление (View).

Представление – это “виртуальная” таблица, которая не совсем существует, но при этом с ней можно обращаться почти на 100% также как и с обычной таблицей.

Как именно превратить json/jsonb колонки с объектами/массивами объектов во View я рассказал вот здесь: https://dba.stackexchange.com/questions/151838/postgresql-json-column-to-view/270546#270546

И повторю здесь (на всякий):

JsonB Array

DO $$
DECLARE l_keys text;
BEGIN
  drop view if exists YOUR_VIEW_NAME cascade;

  select string_agg(distinct format('jerrayel ->> %L as %I',jkey, jkey), ', ')
     into l_keys
  from YOUR_TABLE_NAME, jsonb_array_elements(YOUR_JSONB_COLUMN_NAME) as t(jerrayel), jsonb_object_keys(t.jerrayel) as a(jkey);

  execute 'create view YOUR_VIEW_NAME as select '||l_keys||' from YOUR_TABLE_NAME, jsonb_array_elements(YOUR_JSONB_COLUMN_NAME) as t(jerrayel)';
END$$;

Json Object

do
$$
declare
  l_keys text;
begin
   drop view if exists v_json_view cascade;

   select string_agg(distinct format('json_column ->> %L as %I',jkey, jkey), ', ')
     into l_keys
   from the_table, json_object_keys(json_column) as t(jkey);

   execute 'create view v_json_view as select '||l_keys||' from the_table';
end;
$$
;

Гораздо больше контента и развлечений в Telegram-канале