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 T(
id int,
data jsonb
);
INSERT INTO T VALUES (1,'[{"a":3, "b":"green"} ,{"a":5, "b":"blue"}]');
INSERT INTO T VALUES (2,'[{"a":3, "b":"red"} ,{"a":5, "b":"yellow"}]');
INSERT INTO T VALUES (3,'[{"a":3, "b":"orange"} ,{"a":5, "b":"blue"}]');
1 rows affected
1 rows affected
1 rows affected
SELECT *
FROM (
select *,jsonb_array_elements(data) ->> 'b' val
from t
) t1
WHERE t1.val = ANY (ARRAY['green','yellow'])
| id | data | val |
|---|---|---|
| 1 | [{"a": 3, "b": "green"}, {"a": 5, "b": "blue"}] | green |
| 2 | [{"a": 3, "b": "red"}, {"a": 5, "b": "yellow"}] | yellow |