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_Details" (
"Product_Name" varchar(100),
"Allowed_Transaction_Modes" text,
"Category" varchar(100)
);
create table "Transaction_Modesl" (
"Allowed_Trans_Modes" varchar(100),
"Description" text
);
insert into "Product_Details" values
('Salary_Account', 'ATM,NEFT,UPI,GPay', 'Premium'),
('Savings_Account', 'ATM,UPI, GPay', 'Silver');
insert into "Transaction_Modesl" values
('NEFT', 'National Electronic Funds Transfer'),
('UPI', 'Unified Payments Interface'),
('GPay', 'Google Pay'),
('ATM', 'Automated teller machine');
2 rows affected
4 rows affected
with data as (
select
pd."Product_Name",
pd."Category",
jsonb_agg(
jsonb_build_object(
'code',
tm."Allowed_Trans_Modes",
'description',
tm."Description"
)
) as agg
from
"Product_Details" pd,
"Transaction_Modesl" tm
where
tm."Allowed_Trans_Modes" = any(string_to_array(pd."Allowed_Transaction_Modes", ','))
group by
1, 2)
select
jsonb_agg(
jsonb_build_object(
'Product Name',
"Product_Name",
'Allowed_Transaction_Modes',
agg,
'Category',
"Category"
)
)
from
data
| jsonb_agg |
|---|
| [{"Category": "Premium", "Product Name": "Salary_Account", "Allowed_Transaction_Modes": [{"code": "NEFT", "description": "National Electronic Funds Transfer"}, {"code": "UPI", "description": "Unified Payments Interface"}, {"code": "GPay", "description": "Google Pay"}, {"code": "ATM", "description": "Automated teller machine"}]}, {"Category": "Silver", "Product Name": "Savings_Account", "Allowed_Transaction_Modes": [{"code": "UPI", "description": "Unified Payments Interface"}, {"code": "ATM", "description": "Automated teller machine"}]}] |