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 product (sku jsonb);
create table item (sku numeric);
insert into product values
('{"ps_sku": [149408204]}'),
('{"ps_sku": [149461190]}'),
('{"ps_sku": [149422714]}');
insert into item values
(149408204),
(149461190),
(149422714);
3 rows affected
3 rows affected
select
*
from
product p
cross join jsonb_array_elements_text(sku -> 'ps_sku') as j(sku)
inner join item i on i.sku = j.sku :: numeric
| sku | sku | sku |
|---|---|---|
| {"ps_sku": [149408204]} | 149408204 | 149408204 |
| {"ps_sku": [149461190]} | 149461190 | 149461190 |
| {"ps_sku": [149422714]} | 149422714 | 149422714 |