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 data(
id INTEGER NOT NULL PRIMARY KEY
,owner VARCHAR(7) NOT NULL
,users jsonb
);
INSERT INTO data(id,owner,users) VALUES (1,'alice','[]');
INSERT INTO data(id,owner,users) VALUES (2,'bob','[{"accountId": "alice", "role": "manager"}]');
INSERT INTO data(id,owner,users) VALUES (3,'john','[{"accounId": "bob", "role": "guest"}]');

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1
select d.*
from data d
left join lateral jsonb_array_elements(d.users) as x(js) on 1 = 1
where 'alice' in (d.owner, x.js ->> 'accountId')
id owner users
1 alice []
2 bob [{"role": "manager", "accountId": "alice"}] 
SELECT 2
select d.*
from data d
left join lateral jsonb_array_elements(d.users) as x(js) on 1 = 1
where 'alice' in (d.owner, x.js ->> 'accountId')
id owner users
1 alice []
2 bob [{"role": "manager", "accountId": "alice"}] 
SELECT 2