postgres array_agg with distinct on json_build_object

Question

select 
  t1.id,
  array_agg(
    json_build_object('id', t2.id, 'status', t2.status)
  ) as statuses
from table1 t1
inner join table2 t2 on t1.id=t2.user_id   
inner join table3 t3 on t1.id=t3.user_id
group by t1.id

table1 
id  ,  user
1   ,  'A'
2   ,  'B'

table2
user_id  ,  status
1   ,  'P'
1   ,  'AP'

table3
user_id  ,  something
1   ,  'A12'
1   ,  'B1212'

the table3 also one-many relationship as a result the duplication of statuses coming in the array_agg, i tried with array_agg(distinct json_build_object()) and array_agg(distinct on json_build_object()), how can we prevent duplications in this case ??

Do you need an array of JSON or a JSON array (json_agg instead of array_agg). Please show your expected result. It is quite clear that the status' are duplicated. But what do you want to do with "something" instead? — S-Man, Oct 17 '19 at 06:51
{{id: t2id1,status: 'P' },{id: t2id1,status: 'P' },{id: t2id2,status: 'AP' },{id: t2id2,status: 'AP' }} the current result , i need the output as {{id: t2id1,status: 'P' },{id: t2id2,status: 'AP' }} — Niyaz, Oct 17 '19 at 07:28
This is no valid JSON. Valid JSON would be `[{...},{...},{...}]`; What are you doing with "B1212" value? — S-Man, Oct 17 '19 at 07:29
the B1212 something also i am doing the aggregation , the duplicated real value is "{"{\"id\" : \"bmjb7qvod241422id8eg\", \"name\" : \"Acube+1 PA\", \"status\" : \"Pending\"}","{\"id\" : \"bmjb7qvod241422id8eg\", \"name\" : \"Acube+1 PA\", \"status\" : \"Pending\"}","{\"id\" : \"bmjb7qvod241422id8fg\", \"name\" : \"Acube PA\", \"status\" : \"Pending\"}","{\"id\" : \"bmjb7qvod241422id8fg\", \"name\" : \"Acube PA\", \"status\" : \"Pending\"}"}" — Niyaz, Oct 17 '19 at 07:33
the b1b2 aggregation is as follows "{"{\"id\" : \"bmjb7qvod241422id8g0\", \"status\" : \"Not Shared\", \"principalAccepted\" : false, \"participantAccepted\" : false}","{\"id\" : \"bmjb7qvod241422id8f0\", \"status\" : \"Not Shared\", \"principalAccepted\" : false, \"participantAccepted\" : false}","{\"id\" : \"bmjb7qvod241422id8g0\", \"status\" : \"Not Shared\", \"principalAccepted\" : false, \"participantAccepted\" : false}","{\"id\" : \"bmjb7qvod241422id8f0\", \"status\" : \"Not Shared\", \"principalAccepted\" : false, \"participantAccepted\" : false}"}" — Niyaz, Oct 17 '19 at 07:34

score 1 · Answer 1 · answered Oct 17 '19 at 07:39

Just filter out the relevant status as join condition (1):

select 
  t1.id,
  array_agg(
    json_build_object('id', t2.id, 'status', t2.status)
  ) as statuses
from table1 t1
inner join table2 t2 on t1.id=t2.user_id   
inner join table3 t3 on t1.id=t3.user_id 
   and t3.status = 'A12'                   -- 1.
group by t1.id

Furthermore, if you want to get valid JSON arrays, you should use json_agg() instead of array_agg():

select 
  t1.id,
  json_agg(
    json_build_object('id', t2.id, 'status', t2.status)
  ) as statuses
from table1 t1
inner join table2 t2 on t1.id=t2.user_id   
inner join table3 t3 on t1.id=t3.user_id 
   and t3.status = 'A12'               
group by t1.id

postgres array_agg with distinct on json_build_object

1 Answers1