How to return just distinct values from json.agg() function

Question

I have the following query

select json_agg(t) from (
select  json_build_object(
'field_1',a.field_1,
'field_2',a.field_2,
'field_4',json_agg(json_build_object('id',g.id,'data',g.fullname)),
'field_9',json_agg(json_build_object('id',i.optionid,'data',i.option))
 ) as data

from schema_1.tbl_342 a
left join schema_1.tbl_342_to_tbl_329_field_10 b on a.id=b.tbl_342_id
left join schema_1.tbl_329_customid c on b.tbl_329_id=c.id
left join schema_1.tbl_329_field_23_join d on c.id=d.id
left join schema_1.tbl_329_field_23 e on d.optionid = e.optionid
left join schema_1.tbl_342_to_tbl_312_field_4 f on a.id=f.tbl_342_id
left join schema_1.tbl_312_customid g on f.tbl_312_id = g.id
left join schema_1.tbl_342_field_9_join h on h.id=a.id
left join schema_1.tbl_342_field_9 i on i.optionid=h.optionid   
group by a.field_1,a.field_2
) t

This results in the following JSON format

   [
  {
  "data":{
     "field_1":"John",
     "field_2":null,
     "field_4":[
        {
           "id":null,
           "data":null
        }
     ],
     "field_9":[
        {
           "id":2,
           "data":"Green"
        }
     ]
  }
 },
 {
  "data":{
     "field_1":"Jackson",
     "field_2":null,
     "field_4":[
        {
           "id":2,
           "data":"Marketing Manager M1004"
        },
        {
           "id":4,
           "data":"Senior Javascript Engineer"
        },
        {
           "id":5,
           "data":"Recruiter"
        }
     ],
     "field_9":[
        {
           "id":3,
           "data":"Red"
        },
        {
           "id":3,
           "data":"Red"
        },
        {
           "id":3,
           "data":"Red"
        }
     ]
  }
 },
  {
    "data":{
     "field_1":"Jacob",
     "field_2":null,
     "field_4":[
        {
           "id":null,
           "data":null
        }
     ],
     "field_9":[
        {
           "id":null,
           "data":null
        }
     ]
  }
 },
 {
  "data":{
     "field_1":"Todd",
     "field_2":null,
     "field_4":[
        {
           "id":null,
           "data":null
        }
     ],
     "field_9":[
        {
           "id":4,
           "data":"Yellow"
        }
     ]
  }
 },
 {
  "data":{
     "field_1":"Billy",
     "field_2":null,
     "field_4":[
        {
           "id":5,
           "data":"Recruiter"
        }
     ],
     "field_9":[
        {
           "id":1,
           "data":"Blue"
        }
     ]
   }
 }
 ]

In this example, I'm trying to fix two things.

1.Removing the data node element. I'd like to have the objects start at root, not in Data

2.Notice in the second node. The field_4 has 3 elements, but field_9 has a single red value in DB. Here it's repeating the duplicate values, must be matching the number of returned records from field_4

How can I get just distinct values from this aggregate? I've tried

 'field_4',distinct json_agg(json_build_object('id',g.id,'data',g.fullname)),

and other forms like that, but it doesn't like the syntax. FYI, I'm using PostgreSQL 11

Answer 1

Answers and solution for your problem will be as below:

Problem 1 - You are using json_agg(t) to generate the final JSON data which will pick all column names or alias as key for JSON and aggregate it. So here you should use array_to_json(array_agg(t.data)). array_agg will convert the result of subquery in array and then array_to_json will convert the final array to JSON.

Problem 2 - You want distinct json object in inner array. So Distinct can not be used with json data type because there is no equality operator available in PostgreSQL for JSON type. So you should use JSONB.

Considering your query mentioned in the question is working fine, try it :

select array_to_json(array_agg(t.data)) from (
select  jsonb_build_object(
'field_1',a.field_1,
'field_2',a.field_2,
'field_4',jsonb_agg(distinct jsonb_build_object('id',g.id,'data',g.fullname)),
'field_9',jsonb_agg(distinct jsonb_build_object('id',i.optionid,'data',i.option))
 ) as data

from schema_1.tbl_342 a
left join schema_1.tbl_342_to_tbl_329_field_10 b on a.id=b.tbl_342_id
left join schema_1.tbl_329_customid c on b.tbl_329_id=c.id
left join schema_1.tbl_329_field_23_join d on c.id=d.id
left join schema_1.tbl_329_field_23 e on d.optionid = e.optionid
left join schema_1.tbl_342_to_tbl_312_field_4 f on a.id=f.tbl_342_id
left join schema_1.tbl_312_customid g on f.tbl_312_id = g.id
left join schema_1.tbl_342_field_9_join h on h.id=a.id
left join schema_1.tbl_342_field_9 i on i.optionid=h.optionid   
group by a.field_1,a.field_2
) t