0

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.

1
  • Unrelated, but: you don't need the cast ::jsonb if you use the corresponding jsonb_ functions: jsonb_agg(jsonb_build_object(...)) – a_horse_with_no_name Nov 6 '20 at 9:11
0

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

2
  • I've updated my question, maybe it'll be possible with that extra table. – K. Anye Nov 6 '20 at 6:00
  • Thank you, exactly what I was looking for! – K. Anye Nov 9 '20 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.