How to filter a query based on a jsonb data?

Not even sure if it's possible to do this kind of query in postgres. At least i'm stuck. I have two tables: a product recommendation list, containing multiple products to be recommended to a particular customer; and a transaction table indicating the product bought by customer and transaction details.

I'm trying to track the performance of my recommendation by plotting all the transaction that match the recommendations (both customer and product).

Below is my test case. Kindly help

create table if not exists productRec(  --Product Recommendation list
task_id int,
customer_id int,
detail jsonb);
truncate productRec;
insert into productRec values   (1, 2, '{"1":{"score":5, "name":"KitKat"}, 
                        "4":{"score":2, "name":"Yuppi"}
                        }'),

                    (1, 3, '{"1":{"score":3, "name":"Yuppi"}, 
                        "4":{"score":2, "name":"GoldenSnack"}
                        }'),
                    (1, 4, '{"1":{"score":3, "name":"Chickies"}, 
                        "4":{"score":2, "name":"Kitkat"}
                        }');

drop table txn;
create table if not exists txn( --Transaction table
customer_id int, 
item_id text,
txn_value numeric, 
txn_date date);
truncate txn;
insert into txn values  (1, 'Yuppi', 500, DATE '2001-01-01'), (2, 'Kitkat', 2000, DATE '2001-01-01'), 
                    (3, 'Kitkat', 2000, DATE '2001-02-01'), (4, 'Chickies', 200, DATE '2001-09-01');


--> Query must plot:
--Transaction value vs date where the item_id is inside the recommendation for that customer
--ex: (2000, 2001-01-01), (200, 2001-09-01)

Solution

We can get each recommendation as its own row with jsonb_each. I don't know what to do with the keys so I just take the value (still jsonb) and then the name inside it (the ->> outputs text).

select
    customer_id,
    (jsonb_each(detail)).value->>'name' as name
from productrec

So now we have a list of customer_ids and item_ids they were recommended. Now we can just join this with the transactions.

select
    txn.txn_value,
    txn.txn_date
from txn
join (
    select
        customer_id,
        (jsonb_each(detail)).value->>'name' as name
    from productrec
) p ON (
    txn.customer_id = p.customer_id AND
    lower(txn.item_id) = lower(p.name)
);

In your example data you spelled Kitkat differently in the recommendation table for customer 2. I added lowercasing in the join condition to counter that but it might not be the right solution.

 txn_value |  txn_date
-----------+------------
      2000 | 2001-01-01
       200 | 2001-09-01
(2 rows)