Postgresql, how to SELECT json object without duplicated rows

Question

I'm trying to find out how to get JSON object results of selected rows, and not to show duplicated rows.
My current query:

SELECT DISTINCT ON (vp.id) jsonb_agg(jsonb_build_object('affiliate',a.*)) as affiliates, jsonb_agg(jsonb_build_object('vendor',vp.*)) as vendors FROM
affiliates a
INNER JOIN related_affiliates ra ON a.id = ra.affiliate_id
INNER JOIN related_vendors rv ON ra.product_id = rv.product_id
INNER JOIN vendor_partners vp ON rv.vendor_partner_id = vp.id
WHERE ra.product_id = 79 AND a.is_active = true
GROUP BY vp.id

The results that I receive from this is:

[
affiliates: {
affiliate: affiliate1
affiliate: affiliate2
},
vendors: {
vendor: vendor1,
vendor: vendor1,
}

As you can see in the second record, vendor is still vendor1 because there are no more results, so I'd like to also know if there's a way to remove duplicates.
Thanks.

Your output does not look consistent with the query. There should be one array of affiliates, and another array of vendors - which is not what you are showing here. — GMB
– GMB, Commented Dec 27, 2020 at 11:32

Edouard · Accepted Answer · 2020-12-27 12:42:34Z

5

First point : the result you display here above doesn't conform the json type : the keys are not double-quoted, the string values are not double-quoted, having dupplicated keys in the same json object ('{"affiliate": "affiliate1", "affiliate": "affiliate2"}' :: json) is not be accepted with the jsonb type (but it is with the json type).

Second point : you can try to add the DISTINCT key word directly in the jsonb_agg function :

jsonb_agg(DISTINCT jsonb_build_object('vendor',vp.*))

and remove the DISTINCT ON (vp.id) clause.

You can also add an ORDER BY clause directly in any aggregate function. For more information, see the manual.

edited Dec 27, 2020 at 12:42

answered Dec 27, 2020 at 12:00

Edouard

7,1391 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 0 · Accepted Answer · 2020-12-27 13:18:39Z

0

You could aggregate first, then join on the results of the aggregates:

SELECT a.affiliates, v.vendors 
FROM (
  select af.id, jsonb_agg(jsonb_build_object('affiliate',af.*)) as affiliates
  from affiliates af
  group by af.id
) a  
  JOIN related_affiliates ra ON a.id = ra.affiliate_id
  JOIN related_vendors rv ON ra.product_id = rv.product_id
  JOIN (
    select vp.id, jsonb_agg(jsonb_build_object('vendor',vp.*)) as vendors
    from vendor_partners vp
    group by vp.id
  ) v ON rv.vendor_partner_id = v.id
WHERE ra.product_id = 79 
 AND a.is_active = true

edited Dec 27, 2020 at 13:18

answered Dec 27, 2020 at 11:05

user330315

Collectives™ on Stack Overflow

Postgresql, how to SELECT json object without duplicated rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related