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

Akhilesh Mishra · Accepted Answer · 2020-08-28 14:37:28Z

7

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

edited Aug 28, 2020 at 14:37

answered Aug 27, 2020 at 16:36

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

3 Comments

mike hennessy Over a year ago

Thx! That solved the duplicate issue. The other issue is that, the top node is still "data". If I remove the "as data" it then shows top node as "json_object_build". what I want is for a node to have the format output like : {{"field_1": "Billy", "field_2": null, "field_4": [{"id": 4, "data": "Senior Javascript Engineer"}} . Currently it's {"data":{"field_1": "Billy", "field_2": null, "field_4": [{"id": 4, "data": "Senior Javascript Engineer"}

Akhilesh Mishra Over a year ago

Ya it should be array_to_json(array_agg(t.data)). i have updated the answer

cikatomo Over a year ago

what's the result of this? do keys have name or is it 'f1':value?

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related