Postgresql-选择聚合决策列为json的查询

Question

我有一个包含指定列的表：

id - bigint
decision - varchar(80)
type - varchar(258)

我想做一个选择查询，结果返回如下(id，decisionsValues，count为json，type(：

id  decisions                                                                         type
1  {"firstDecisionsValue":countOfThisValue, "secondDecisionsValue": countOfThisValue} entryType

我听说我可以尝试使用json_agg，但它不允许COUNT方法，尝试使用带查询的json_agg：

SELECT ac.id,
json_agg(ac.decision),
ac.type
FROM myTable ac
GROUP BY ac.id, ac.type;

但以此结尾(对于id为1的条目，有两次出现firstDecisionsValue，一次出现secondDecisionsValue(：

id  decisions                                                             type
1  {"firstDecisionsValue", "firstDecisionsValue", "secondDecisionsValue"} entryType

最小可重复性示例

CREATE TABLE myTable
(
id        bigint,
decisions varchar(80),
type      varchar(258)
);
INSERT INTO myTable
VALUES (1, 'firstDecisionsValue', 'myType');
INSERT INTO myTable
VALUES (1, 'firstDecisionsValue', 'myType');
INSERT INTO myTable
VALUES (1, 'secondDecisionsValue', 'myType');

你能为我提供一些如何按预期制作的技巧吗

1, {"fistDecisionsValue":2, "secondDecisionsValue":1}, entryType

Answer 1

你可以试试这个

SELECT a.id, jsonb_object_agg(a.decisions, a.count), a.type
FROM 
( SELECT id, type, decisions, count(*) AS count
FROM myTable
GROUP BY id, type, decisions
) AS a
GROUP BY a.id, a.type

请参阅dbfiddle中的结果。

Answer 2

首先，您应该为每个决策计算id, type, decisions的计数然后，您应该使用jsonb_object_agg来创建JSON。

演示

with data as (
select 
ac.id,
ac.type,
ac.decisions,
count(*)
from 
myTable ac
group by 
ac.id, 
ac.type,
ac.decisions
)
select 
d.id,
d.type,
json_object_agg(d.decisions, d.count)
from 
data d
group by 
d.id, 
d.type