Блог

Главная - Вопросы по программированию - Postgres присоединяет таблицы к столбцу массива JSONB

Postgres присоединяет таблицы к столбцу массива JSONB

#sql #arrays #json #postgresql

#sql #массивы #json #postgresql

Вопрос:

У меня есть поле с этими данными в bonuses.rules:

 [
  {
    "rule":{
        "providers":[
          "11",
          "12",
        ]
    }
}

]

Содержимое «правило-> поставщики» является массивом. выше приведен пример.

Я хочу присоединиться к этим поставщикам с помощью provider_games.provider_id.

это мой запрос, но возвращается пустой результат.

 SELECT bonuses.rules, bonuses.bonus_id, provider_games.provider_id
FROM bonuses
          inner JOIN provider_games ON bonuses.rules -> '[{"rule": {"providers"}}]'
     @> json_build_array (json_build_object ('provider_id', provider_games.provider_id)) :: jsonb

таблица provider_games:

 create table provider_games
(
    provider_game_id bigserial not null
        constraint provider_games_pkey
            primary key
);

alter table provider_games owner to defaultdb;

таблица бонусов:

 create table bonuses
(
    bonus_id bigserial not null
        constraint bonuses_pkey
            primary key,
    rules jsonb
);

alter table bonuses owner to defaultdb;

Комментарии:

1. bonuses.rules -> '[{"rule": {"providers"}}]' выглядит неправильно.

2. Как следует обрабатывать несколько объектов правил в этом массиве?

3. Спасибо за ваш ответ. Я хочу комбинацию providers_id и provider_games.providers_id в bonuses.rules.

Ответ №1:

Вместо JOIN редактирования @> , я бы рекомендовал выполнять объединения против боковых подзапросов, которые отменяют json:

 SELECT bonuses.rules, bonuses.bonus_id, provider_games.provider_game_id
FROM bonuses,
LATERAL jsonb_array_elements(bonuses.rules) rules(obj),
LATERAL jsonb_array_elements_text(obj -> 'rule' -> 'providers') rule_provider(id)
INNER JOIN provider_games ON provider_games.provider_game_id = rule_provider.id::bigint;

(Онлайн-демонстрация)