PostgreSQL json array of objects, default 0 values for properties if not found

Question

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.

• user1822: Unrelated, but: you don't need the cast ::jsonb if you use the corresponding jsonb_ functions: jsonb_agg(jsonb_build_object(...))

Answer 1

IMHO You can't get this outcome with the current data.

Given this sample data:

create table product (product_id int, product_name text);
create table stock (product_id int, location text, quantity int);
insert into product values (1, 'Product 1');
insert into stock values (1, 'a', 10), (1, 'b', 20);

You can't get locations c and d because there is not such information in your data:

select 
 dk.product_id, 
 dk.product_name,
 dr.location,
 dr.quantity
from 
 product dk 
left join 
 stock dr 
 on dr.product_id = dk.product_id;
product_id | product_name | location | quantity
---------: | :----------- | :------- | -------:
 1 | Product 1 | a | 10
 1 | Product 1 | b | 20

You should add a new (or existing) table of locations, or a table valued function, just to be able to get the required data.

create table location (id text);
insert into location values ('a'),('b'),('c'),('d'),('e'),('f');

Now you can use a sub-query to get this information:

select 
 p.product_id,
 p.product_name,
 (
 select 
 json_agg(
 json_build_object('location', l.id, 
 'quantity', coalesce(s.quantity, 0)))::jsonb stock
 from
 location l
 left join
 stock s
 on s.location = l.id
 and s.product_id = p.product_id
 where
 l.id in ('a', 'b', 'c', 'd')
 ) stock
from
 product p

product_id | product_name | stock 
---------: | :----------- | :-----------------------------------------------------------------------------------------------------------------------------------------
 1 | Product 1 | [{"location": "a", "quantity": 10}, {"location": "b", "quantity": 20}, {"location": "c", "quantity": 0}, {"location": "d", "quantity": 0}]

db<>fiddle here

Update

You can use sites table for that purpose in this way:

select 
 dk.product_id,
 dk.product_name,
 (
 select 
 json_agg(
 json_build_object('location', s.location,
 'quantity', coalesce(dr.quantity, 0)))::jsonb stock
 from
 sites s
 left join
 stock dr
 on dr.location = s.location
 and dr.product_id = dk.product_id
 where
 s.site_id = 1
 ) stock
from
 product dk;

db<>fiddle here

• K. Anye: I've updated my question, maybe it'll be possible with that extra table.
• K. Anye: Thank you, exactly what I was looking for!