CODEWITHSUNDEEP
sqlarraysjsonpostgresql
omero
omero

Reputation: 113

Postgres join tables array JSONB column

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;

Upvotes: 0

Views: 979

Answers (1)

Bergi
Bergi

Reputation: 665455

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)

Upvotes: 2

Related Questions