Postgresql: how to select from map of multiple values

Question

I have a SOME_DELTA table which records all party related transactions with amount change Ex.:

PARTY_ID   | SOME_DATE  | AMOUNT
--------------------------------
party_id_1 | 2019-01-01 | 100
party_id_1 | 2019-01-15 | 30
party_id_1 | 2019-01-15 | -60
party_id_1 | 2019-01-21 | 80
party_id_2 | 2019-01-02 | 50
party_id_2 | 2019-02-01 | 100

I have a case where where MVC controller accepts map someMap(party_id, some_date) and I need to get part_id list with summed amount till specific some_date

In this case if I send mapOf("party_id_1" to Date(2019 - 1 - 15), "party_id_2" to Date(2019 - 1 - 2)) I should get list of party_id with summed amount till some_date

Output should look like:

party_id_1 | 70
party_id_2 | 50

Currently code is:

select sum(amount) from SOME_DELTA where party_id=:partyId and some_date <= :someDate

But in this case I need to iterate through map and do multiple DB calls for summed amount for eatch party_id till some_date which feels wrong

Is there a more delicate way to get in one select query? (to avoid +100 DB calls)

score 1 · Accepted Answer · answered Feb 04 '20 at 07:52

You can use a lateral join for this:

select map.party_id, 
       c.amount
from (
  values 
     ('party_id_1', date '2019-01-15'), 
     ('party_id_2', date '2019-01-02')
) map (party_id, cutoff_date)
  join lateral (
    select sum(amount) amount
    from some_delta sd
    where sd.party_id = map.party_id
      and sd.some_date <= map.cutoff_date
  ) c on true
order by map.party_id;

Online example

Postgresql: how to select from map of multiple values

1 Answers1