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.

Unrelated, but: you don't need the cast ::jsonb if you use the corresponding jsonb_ functions: jsonb_agg(jsonb_build_object(...)) — user1822
– user1822, Commented Nov 6, 2020 at 9:11

McNets · Accepted Answer · 2020-11-06 08:38:30Z

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

I've updated my question, maybe it'll be possible with that extra table. — K. Anye
– K. Anye, Commented Nov 6, 2020 at 6:00

Stack Exchange Network

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

1 Answer 1

Hot Network Questions

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

1 Answer 1

Related

Hot Network Questions