Блог

У меня есть следующий запрос

 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
  

Это приводит к следующему JSON формату

    [
  {
  "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"
        }
     ]
   }
 }
 ]
  

В этом примере я пытаюсь исправить две вещи.

1. Удаление data элемента node. Я бы хотел, чтобы объекты начинались с root, а не в Data

2. Обратите внимание на второй узел. field_4 Имеет 3 элемента, но field_9 имеет единственное red значение в БД. Здесь он повторяет повторяющиеся значения, должно соответствовать количеству возвращенных записей из field_4

Как я могу получить только разные значения из этого агрегата? Я пытался

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

и другие подобные формы, но им не нравится синтаксис.
К вашему сведению, я использую PostgreSQL 11

Ответы и решение вашей проблемы будут такими, как показано ниже:

Проблема 1 — Вы используете json_agg(t) для генерации окончательных JSON данных, которые будут выбирать имена всех столбцов или псевдонимы в качестве ключа для JSON и агрегировать их. Итак, здесь вы должны использовать array_to_json(array_agg(t.data)) . array_agg преобразует результат подзапроса в массив, а затем array_to_json преобразует конечный массив в JSON .

Проблема 2 — Вы хотите distinct json object во внутреннем массиве. Поэтому Distinct не может использоваться с json типом данных, потому что в equality operator для PostgreSQL типа нет JSON доступных. Поэтому вам следует использовать JSONB .

Учитывая, что ваш запрос, упомянутый в вопросе, работает нормально, попробуйте :

 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