Intereting Posts
Передача операторов SQL для запроса через параметр iReport SQL Server: резервное копирование всех баз данных rails scope и объединяет Каскадные удаления / обновления с использованием JPA или внутри базы данных? Разница между предложениями ON и WHERE в таблицах SQL-таблицы C # Выполнить инструкцию SQL для использования параметров Как правильно использовать Oracle ORDER BY и ROWNUM? Подсчитайте, достиг ли пользователь предельный лимит Как выбрать столбец из всех таблиц, в которых он находится? Логически невозможно получить эту конкретную строку.? MySQL "CREATE TABLE, ЕСЛИ НЕ СУЩЕСТВУЕТ" -> Ошибка 1050 SQL Query, который возвращает таблицу, в которой каждая строка представляет дату в заданном диапазоне Включить полнотекстовый поиск в режиме просмотра с помощью Inner Join Как разделить содержимое ячейки и извлечь информацию в новый столбец в инструкции выбора SQL? Случай с несколькими условиями в SQL Server 2014

Объединение таблиц с использованием значения внутри столбца JSONB

Есть две таблицы:

Авторизованные контакты ( auth_contacts ):

 ( userid varchar contacts jsonb ) 

contacts содержат массив контактов с атрибутами {contact_id, type}

discussion :

 ( contact_id varchar discussion_id varchar discussion_details jsonb ) 

В таблице auth_contacts не менее 100 тыс. Записей, что делает его не подходящим для JSONB-типа, поскольку он удваивает или утроит количество записей.

Примеры данных для auth_contacts :

 userid | contacts '11111' | '{"contact": [{"type": "type_a", "contact_id": "1-A-12"} , {"type": "type_b", "contact_id": "1-A-13"}]}' 

таблица discussion имеет 5 миллионов нечетных записей.

Я хочу присоединиться к discussion.contact_id (реляционный столбец) с идентификатором контакта, который json-объект внутри массива json-объектов в auth_contacts.contacts .

Один очень грубый способ:

 SELECT * FROM discussion d JOIN (SELECT userid, JSONB_OBJECT_KEYS(a.contacts) AS auth_contact FROM auth_contacts a) AS contacts ON (d.contact_id = contacts.auth_contact::text) 

То, что это делает, на самом деле во время выполнения create (inner sql) userid vs table идентификатора контакта (это то, чего я избегал, и, следовательно, пошел на тип данных JSONB. Этот запрос для пользователя с большими записями занимает 26 секунд, что не очень хорошо. несколько других способов: PostgreSQL 9.4: таблица агрегирования / объединения идентификатора поля JSON внутри массива

Но должен быть более чистый и лучший способ, который был бы таким же простым, как JOIN d.contact_id = contacts -> contact -> contact_id? Когда я пытаюсь это сделать, он не дает никаких результатов.

При поиске в сети это кажется довольно громоздкой задачей?

Доказательство концепции

Ваш «грубый способ» на самом деле не функционирует. Вот грубый способ, который действительно работает:

 SELECT * FROM auth_contacts a , jsonb_to_recordset(a.contacts->'contact') AS c(contact_id text) JOIN discussion d USING (contact_id); 

И, как было прокомментировано, вы также можете сформулировать условие объединения с оператором contains @> :

 SELECT * FROM auth_contacts a JOIN discussion d ON a.contacts->'contact' @> json_build_array( json_build_object('contact_id', d.contact_id) )::jsonb 

Но скорее используйте функции создания JSON, чем конкатенация строк. Выглядит громоздко, но на самом деле будет очень быстро, если будет поддерживаться функциональный индекс jinb_path_ops GIN :

 CREATE INDEX auth_contacts_contacts_gin_idx ON auth_contacts USING gin ((contacts->'contact') jsonb_path_ops); 

Детали:

  • Индекс для поиска элемента в массиве JSON
  • Postgres 9.4 jsonb array в качестве таблицы

Правильное решение

Это все увлекательно, но проблема здесь в реляционной модели. Ваше требование:

следовательно, для него не подходит тип JSONB, который не подходит, поскольку он удваивает или утроит количество записей.

является противоположностью того, что правильно . Это бессмыслица для переноса идентификаторов, необходимых для объединения таблиц в тип документа JSON. Нормализовать таблицу со отношениями «многие ко многим» и реализовать все идентификаторы, с которыми вы работаете, внутри БД, как простые типы данных Postgres. Основные инструкции:

  • Как выполнять операции обновления столбцов типа JSONB в Postgres 9.4
  • Как реализовать отношения «многие ко многим» в PostgreSQL?