1

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(...)) Commented Nov 6, 2020 at 9:11

1 Answer 1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.