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