Postgres join tables array JSONB column

Question

I have a field with the this data in bonuses.rules:

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

]

The content of "rule-> providers" is array. there is an example above.

I want to join these providers with provider_games.provider_id.

this is my query, but the blank result is returned.

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 table:

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

alter table provider_games owner to defaultdb;

bonuses table:

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

alter table bonuses owner to defaultdb;

Thanks your reply. I want the combination of providers_id and provider_games.providers_id in bonuses.rules. — omero
– omero, Commented Sep 15, 2020 at 19:16

Bergi · Accepted Answer · 2020-09-15 22:16:13Z

2

Instead of JOINing on a @>, I would recommend to do joins against lateral subqueries that unnest the 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;

^{(Online demo)}

answered Sep 15, 2020 at 22:16

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres join tables array JSONB column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related