Given the following data:
create table datas (id int, data jsonb);
insert into categories (id, budget)
values
(1, '{"2022-07-01": {"budget_amount": value1, "some": "thing1"},"2022-08-01": {"budget_amount": value2, "some": "thing2"}}'),
(2, '{"2022-08-01": {"budget_amount": value3, "some": "thing3"},"2022-08-01": {"budget_amount": value4, "some": "thing4"}}');
insert into transactions (id, transaction)
values
(1, '{"vendor","equalto.categories.id2",amount, date}'),
(2, '{"vendor","equalto.categories.id1",amount, date}'),
(3, '{"vendor","equalto.categories.id3",amount, date}'),
(4, '{"vendor","equalto.categories.id2",amount, date}');
| id | budget |
|---|---|
| 1 | {"2022-07-01": {"budget_amount": value1, "some": "thing1"},"2022-08-01": {"budget_amount": value2, "some": "thing2"}} |
| 2 | {"2022-08-01": {"budget_amount": value3, "some": "thing3"},"2022-08-01": {"budget_amount": value4, "some": "thing4"}} |
| id | vendor | category_id | amount | date |
|---|---|---|---|---|
| 1 | vendor | equalto.categories.id1 | amount | date |
| 2 | vendor | equalto.categories.id2 | amount | date |
| 3 | vendor | equalto.categories.id3 | amount | date |
| 4 | vendor | equalto.categories.id2 | amount | date |
For a particular category I'm looking to do something like the following:
SELECT categories_id, DATE_TRUNC('month',transaction.date) as tmonth, SUM(transactions.amount),
jsonb_path_query(budget.data, '$.*.budget_amount') #>> '{}' as budget_amount
from categories, transactions
JOIN tmonth on jsonb_path_query(budget.data, '$.keyvalue()') ->> 'key'
| Category ID | Month | budget_amount | spend |
|---|---|---|---|
| categories.id | DATE_TRUNC('month',transaction.date) | budget_amount | sum(transactions.amount) |
Essentially I'm looking to join the top level key in datas (first of the month in YYYY-MM-DD format) to the column date (timestamp) in the transaction table. The goal being to get per row the budget.id, month as YYYY-MM-01, budget, sum of monthly transactions for that budget.id
Issue being that I can't figure out how to join the two tables on a jsonb column. Could someone please recommend a way to combine this data? Any help would be appriciated.
