sql - Postgres 连接表数组 JSONB 列

Question

我在 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;

标签： sqlarraysjsonpostgresql

Answer 1

而不是JOINing on a @>，我建议对取消嵌套 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;

^{（在线演示）}