So my query looks like this:
select dk.product_id, dk.product_name,
json_agg(
json_build_object('location', dr.location, 'quantity', dr.quantity))::jsonb stock
from dax.dx_k dk
left join dax.dx_r dr on dr.product_id= dk.product_id
where dr.location in ('A', 'B', 'C', 'D')
group by product_name, product_id
order by product_id
And this results in:
00015
TEST PRODUCT NAME
[
{"location": "A", "quantity": 15000.000000000000},
{"location": "B", "quantity": 0.000000000000}
]
And my goal is to get the stock for all locations even if it doesn't have a record for C and D I'd like to return C = 0 and D = 0.
Like this:
[
{"location": "A", "quantity": 1500},
{"location": "B", "quantity": 0},
{"location": "C", "quantity": 0},
{"location": "D", "quantity": 0}
]
Is there a simple way to do this, because I don't want to overcomplicate the query unnecessarily?
UPDATE:
I have another table called sites which contains the locations to any given site and I'll include my site_id in the WHERE clause, e.g.
...
WHERE site_id = 1
will result in:
site_id|location
1 |A
1 |B
1 |C
1 |D
And I'll need all locations for the given site:
[
{"location": "A", "quantity": 1500},
{"location": "B", "quantity": 0},
{"location": "C", "quantity": 0},
{"location": "D", "quantity": 0}
]
The problem with this is that I have my products associated with the locations in dx_r on product_id.
::jsonbif you use the correspondingjsonb_functions:jsonb_agg(jsonb_build_object(...))