#arrays #postgresql #jsonb
#массивы #postgresql #jsonb
Вопрос:
Я динамически создаю некоторые запросы, которые выглядят следующим образом
select array_to_json(array_agg(t)) from (
select
a.field_1,
a.field_2,
jsonb_agg(distinct jsonb_build_object('id',g.id,'data',g.fullname)) as field_4,
jsonb_agg(distinct jsonb_build_object('id',i.optionid,'data',i.option)) as field_9,
jsonb_agg(distinct jsonb_build_object('id',c.id,'data',c.fullname)) as field_6,
jsonb_agg(distinct jsonb_build_object('id',k.id,'data',k.fullname)) as field_8,
jsonb_agg(distinct jsonb_build_object('id',m.id,'data',m.field_20)) as field_21,
l.field_5,
jsonb_agg(distinct jsonb_build_object('id',e.optionid,'data',e.option,'attributes',e.attributes)) as field_23,
n.data as field_13,
jsonb_agg(distinct jsonb_build_object('id',bb.id,'data',bb.fullname)) as field_12
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 m on m.id=b.tbl_329_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
left join schema_1.tbl_343_to_tbl_342_field_6 j on j.tbl_342_id=a.id
left join schema_1.tbl_343_customid k on j.tbl_343_id=k.id
left join schema_1.tbl_343 l on l.id=j.tbl_343_id
left join schema_1.tbl_342_field_13_max n on n.id=a.id
group by a.field_1,a.field_2,l.field_5,m.field_23,n.data
) t
пример вывода возвращается следующим образом
[{"field_1":"Billy","field_2":null,"field_4":[{"id": 4, "data": "Senior Javascript Engineer"}, {"id": 5, "data": "Recruiter"}],"field_9":[{"id": 1, "data": "Blue"}],"field_6":[{"id": 32, "data": "Nancy - 32"}],"field_8":[{"id": null, "data": null}],"field_21":[{"id": 32, "data": "ggñ@cnn.com"}],"field_5":null,"field_23":[{"id": 5, "data": "Expert", "attributes": {"text": "#FFFFFF", "color": "#448AFF"}}],"field_13":"Expert"},{"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"}],"field_6":[{"id": 36, "data": ",Mike - 36"}],"field_8":[{"id": 2, "data": "Henry"}],"field_21":[{"id": 36, "data": "PtereSwanson@gmail.com"}],"field_5":"CA","field_23":[{"id": 6, "data": "Guru", "attributes": {"text": "#FFFFFF", "color": "#0D47A1"}}],"field_13":"Guru"},{"field_1":"Jacob","field_2":null,"field_4":[{"id": null, "data": null}],"field_9":[{"id": null, "data": null}],"field_6":[{"id": null, "data": null}],"field_8":[{"id": null, "data": null}],"field_21":[{"id": null, "data": null}],"field_5":null,"field_23":[{"id": null, "data": null, "attributes": null}],"field_13":null},{"field_1":"John","field_2":null,"field_4":[{"id": null, "data": null}],"field_9":[{"id": 2, "data": "Green"}],"field_6":[{"id": 32, "data": "Nancy - 32"}, {"id": 35, "data": "Bobby - 35"}],"field_8":[{"id": null, "data": null}],"field_21":[{"id": 32, "data": "ggñ@cnn.com"}, {"id": 35, "data": "Oliver@yahoo.com"}],"field_5":null,"field_23":[{"id": 4, "data": "Experienced", "attributes": {"text": "#FFFFFF", "color": "#C79702"}}, {"id": 5, "data": "Expert", "attributes": {"text": "#FFFFFF", "color": "#448AFF"}}],"field_13":"Expert"},{"field_1":"Todd","field_2":null,"field_4":[{"id": null, "data": null}],"field_9":[{"id": 4, "data": "Yellow"}],"field_6":[{"id": 1, "data": "Bobbie - 1"}, {"id": 4, "data": "bobby - 4"}],"field_8":[{"id": 1, "data": "Please stand up"}],"field_21":[{"id": 1, "data": "paul@sonos.com"}, {"id": 4, "data": "ggñ@cnn.com"}],"field_5":"MA","field_23":[{"id": 2, "data": "Novice", "attributes": {"text": "#000000", "color": "#FFEB3B"}}, {"id": 3, "data": "Intermediate", "attributes": {"text": "#000000", "color": "#F5F5F5"}}],"field_13":"Intermediate"}]
Вот результат, который я хочу получить.
{count: 5, data:[{"field_1":"Billy","field_2":null,"field_4":[{"id": 4, "data": "Senior Javascript Engineer"}, {"id": 5, "data": "Recruiter"}],"field_9":[{"id": 1, "data": "Blue"}],"field_6":[{"id": 32, "data": "Nancy - 32"}],"field_8":[{"id": null, "data": null}],"field_21":[{"id": 32, "data": "ggñ@cnn.com"}],"field_5":null,"field_23":[{"id": 5, "data": "Expert", "attributes": {"text": "#FFFFFF", "color": "#448AFF"}}],"field_13":"Expert"},{"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"}],"field_6":[{"id": 36, "data": ",Mike - 36"}],"field_8":[{"id": 2, "data": "Henry"}],"field_21":[{"id": 36, "data": "PtereSwanson@gmail.com"}],"field_5":"CA","field_23":[{"id": 6, "data": "Guru", "attributes": {"text": "#FFFFFF", "color": "#0D47A1"}}],"field_13":"Guru"},{"field_1":"Jacob","field_2":null,"field_4":[{"id": null, "data": null}],"field_9":[{"id": null, "data": null}],"field_6":[{"id": null, "data": null}],"field_8":[{"id": null, "data": null}],"field_21":[{"id": null, "data": null}],"field_5":null,"field_23":[{"id": null, "data": null, "attributes": null}],"field_13":null},{"field_1":"John","field_2":null,"field_4":[{"id": null, "data": null}],"field_9":[{"id": 2, "data": "Green"}],"field_6":[{"id": 32, "data": "Nancy - 32"}, {"id": 35, "data": "Bobby - 35"}],"field_8":[{"id": null, "data": null}],"field_21":[{"id": 32, "data": "ggñ@cnn.com"}, {"id": 35, "data": "Oliver@yahoo.com"}],"field_5":null,"field_23":[{"id": 4, "data": "Experienced", "attributes": {"text": "#FFFFFF", "color": "#C79702"}}, {"id": 5, "data": "Expert", "attributes": {"text": "#FFFFFF", "color": "#448AFF"}}],"field_13":"Expert"},{"field_1":"Todd","field_2":null,"field_4":[{"id": null, "data": null}],"field_9":[{"id": 4, "data": "Yellow"}],"field_6":[{"id": 1, "data": "Bobbie - 1"}, {"id": 4, "data": "bobby - 4"}],"field_8":[{"id": 1, "data": "Please stand up"}],"field_21":[{"id": 1, "data": "paul@sonos.com"}, {"id": 4, "data": "ggñ@cnn.com"}],"field_5":"MA","field_23":[{"id": 2, "data": "Novice", "attributes": {"text": "#000000", "color": "#FFEB3B"}}, {"id": 3, "data": "Intermediate", "attributes": {"text": "#000000", "color": "#F5F5F5"}}],"field_13":"Intermediate"}]}
Я хотел бы, чтобы общее количество записей возвращалось в атрибуте верхнего уровня отдельно от результатов данных.
Как бы я это сделал, я попытался добавить count(a.id) , которая является базовой таблицей, но она не возвращает общее количество записей.
Комментарии:
1. Для получения точного запроса требуются образцы данных и желаемый результат
2. Хорошо, я обновил тикет примером текущего вывода, а затем тем, что я хотел бы сгенерировать. Дайте мне знать, если этого достаточно.
3. добавлен ответ.
4. Что вы подразумеваете под » общим количеством записей «? Просто
select count(t), array_to_json(array_agg(t)) as data from (…) tпомогло бы?5. Кстати, я бы предложил использовать подзапросы с
jsonb_aggвSELECTвместо этих многихLEFT JOIN, чтобы получить более читаемую структуру запроса.
Ответ №1:
Используйте, json_array_length поскольку ваш окончательный вывод находится в JSON Array формате.
select jsonb_build_object('count',json_array_length(data),'data',data) from (
select array_to_json(array_agg(t)) data from (
select
a.field_1,
a.field_2,
jsonb_agg(distinct jsonb_build_object('id',g.id,'data',g.fullname)) as field_4,
jsonb_agg(distinct jsonb_build_object('id',i.optionid,'data',i.option)) as field_9,
jsonb_agg(distinct jsonb_build_object('id',c.id,'data',c.fullname)) as field_6,
jsonb_agg(distinct jsonb_build_object('id',k.id,'data',k.fullname)) as field_8,
jsonb_agg(distinct jsonb_build_object('id',m.id,'data',m.field_20)) as field_21,
l.field_5,
jsonb_agg(distinct jsonb_build_object('id',e.optionid,'data',e.option,'attributes',e.attributes)) as field_23,
n.data as field_13,
jsonb_agg(distinct jsonb_build_object('id',bb.id,'data',bb.fullname)) as field_12
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 m on m.id=b.tbl_329_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
left join schema_1.tbl_343_to_tbl_342_field_6 j on j.tbl_342_id=a.id
left join schema_1.tbl_343_customid k on j.tbl_343_id=k.id
left join schema_1.tbl_343 l on l.id=j.tbl_343_id
left join schema_1.tbl_342_field_13_max n on n.id=a.id
group by a.field_1,a.field_2,l.field_5,m.field_23,n.data
) t)x
Это даст вам желаемый результат
ОБНОВЛЕНИЕ согласно комментарию
with cte as (
select
a.field_1,
a.field_2,
jsonb_agg(distinct jsonb_build_object('id',g.id,'data',g.fullname)) as field_4,
jsonb_agg(distinct jsonb_build_object('id',i.optionid,'data',i.option)) as field_9,
jsonb_agg(distinct jsonb_build_object('id',c.id,'data',c.fullname)) as field_6,
jsonb_agg(distinct jsonb_build_object('id',k.id,'data',k.fullname)) as field_8,
jsonb_agg(distinct jsonb_build_object('id',m.id,'data',m.field_20)) as field_21,
l.field_5,
jsonb_agg(distinct jsonb_build_object('id',e.optionid,'data',e.option,'attributes',e.attributes)) as field_23,
n.data as field_13,
jsonb_agg(distinct jsonb_build_object('id',bb.id,'data',bb.fullname)) as field_12
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 m on m.id=b.tbl_329_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
left join schema_1.tbl_343_to_tbl_342_field_6 j on j.tbl_342_id=a.id
left join schema_1.tbl_343_customid k on j.tbl_343_id=k.id
left join schema_1.tbl_343 l on l.id=j.tbl_343_id
left join schema_1.tbl_342_field_13_max n on n.id=a.id
group by a.field_1,a.field_2,l.field_5,m.field_23,n.data
),
cte2 as
(select count(*) as "count_" from cte)
select row_to_json(x) from (
select cte2.count_ "count", array_to_json(array_agg(t)) as data from (
select * from cte limit 3 -- you can add desired limit here
) t, cte2
group by 1
)x
Комментарии:
1. Ну, мне действительно нужно количество из запроса, потому что я буду добавлять предложение where и потому, что в конечном итоге я верну ограниченное количество записей из большего набора записей. Например, в запросе может совпадать 15 000 записей, но возвращаются только первые 500 записей. В этом примере я хочу показать 15 000, общее количество совпадающих записей из запроса. Как бы я это сделал?
2. вы можете добавить предложение where перед group by
3. да, но разве ваша функция json_array_length (data) не вернет 500 в моем примере, а не 15 000?
4. означает, что вы хотите общее количество записей, а затем только данные, отфильтрованные с помощью предложения where?
5. в качестве примера я добавлю ограничение 500 к приведенному выше запросу. Итак, даже если совпадающих записей может быть 15 000, я верну первые 500, и у меня будет подкачка, чтобы перейти к следующим 500 и так далее. но когда запрос выполняется, я хочу показать общее количество записей запроса. Возможно, это можно сделать только с помощью 2 отдельных запросов