PostgreSQL json array of objects, default 0 values for properties if not found 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   
[
  {&quot;location&quot;: &quot;A&quot;, &quot;quantity&quot;: 15000.000000000000}, 
  {&quot;location&quot;: &quot;B&quot;, &quot;quantity&quot;: 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:
[
  {&quot;location&quot;: &quot;A&quot;, &quot;quantity&quot;: 1500}, 
  {&quot;location&quot;: &quot;B&quot;, &quot;quantity&quot;: 0},
  {&quot;location&quot;: &quot;C&quot;, &quot;quantity&quot;: 0}, 
  {&quot;location&quot;: &quot;D&quot;, &quot;quantity&quot;: 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:
[
  {&quot;location&quot;: &quot;A&quot;, &quot;quantity&quot;: 1500}, 
  {&quot;location&quot;: &quot;B&quot;, &quot;quantity&quot;: 0},
  {&quot;location&quot;: &quot;C&quot;, &quot;quantity&quot;: 0}, 
  {&quot;location&quot;: &quot;D&quot;, &quot;quantity&quot;: 0}
]

The problem with this is that I have my products associated with the locations in dx_r on product_id.
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    | [{&quot;location&quot;: &quot;a&quot;, &quot;quantity&quot;: 10}, {&quot;location&quot;: &quot;b&quot;, &quot;quantity&quot;: 20}, {&quot;location&quot;: &quot;c&quot;, &quot;quantity&quot;: 0}, {&quot;location&quot;: &quot;d&quot;, &quot;quantity&quot;: 0}]


db&lt;&gt;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&lt;&gt;fiddle here