add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table provider_games (
provider_game_id bigserial not null
constraint provider_games_pkey primary key
);

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

INSERT INTO bonuses (rules) VALUES ('[{"rule":{"providers":["11", "12"]}}]');
INSERT INTO provider_games (provider_game_id) SELECT generate_series(5, 15);

TABLE bonuses;
TABLE provider_games;
CREATE TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 11
bonus_id rules
1 [{"rule": {"providers": ["11", "12"]}}] 
SELECT 1
provider_game_id
5
6
7
8
9
10
11
12
13
14
15 
SELECT 11
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;
rules bonus_id provider_game_id
[{"rule": {"providers": ["11", "12"]}}] 1 11
[{"rule": {"providers": ["11", "12"]}}] 1 12 
SELECT 2