如何在PostgreSQL中使用可变数量的键聚合JSON数组？

I have a series of rows in a PostgreSQL table which look like this:

我在PostgreSQL表中有一系列行,如下所示:

-[ RECORD 1 ]---------------------------------------------------------------------
student     | e04c0ae4709340cb8e03c52f444e723f
group       | 1
subgroup    | 1
variable    | VAR1
status      | { "track_A" : "Done", "track_B" : "Done", "track_C" : "To Do" }
-[ RECORD 2 ]---------------------------------------------------------------------
student     | e04c0ae4709340cb8e03c52f444e723f
group       | 1
subgroup    | 1
variable    | VAR2
status      | { "track_A" : "To Do", "track_B" : "Done", "track_C" : "To Do" }
-[ RECORD 3 ]---------------------------------------------------------------------
student     | 849d1e6a0c2b4530a2b550829df94556
group       | 0
subgroup    | 1
variable    | VAR3
status      | { "track_A" : "Done", "track_B" : "To Do", "track_C" : "To Do" }

I would like to group them by student, group and subgroup and get a count status for each track. Something like:

我想按学生,小组和小组对它们进行分组,并获得每个曲目的计数状态。就像是:

-[ RECORD 1 ]---------------------------------------------------------------------
student     | e04c0ae4709340cb8e03c52f444e723f
group       | 1
subgroup    | 1
totals      | { "track_A" : {"done": 1, "to_do": 1}, {"track_B" : {"done": 0, "to_do": 2}, "track_C" : {"done": 0, "to_do": 2} }

The issue is that the number of tracks can vary. I do know their names, but they are not static, so I cannot do a simple aggregation. Any suggestions how I could write this in PostgreSQL (9.5)? I do not want to iterate over all the tracks and aggregate, as the operation will take some time.

问题是曲目的数量可能会有所不同。我知道他们的名字,但他们不是静态的,所以我不能做一个简单的聚合。有任何建议我如何在PostgreSQL(9.5)中写这个?我不想迭代所有曲目并聚合,因为操作需要一些时间。

1 个解决方案

#1

You could use json_each_text to "unest" values and json_object_agg to combine it again.

您可以使用json_each_text来“解除”值和json_object_agg再次组合它。

Data:

DROP TABLE IF EXISTS tab;
CREATE TABLE tab(student VARCHAR(36), "group" INT, subgroup INT,
                 variable VARCHAR(20), status JSON);        

INSERT INTO tab(student, "group", subgroup, variable, status)
VALUES
('e04c0ae4709340cb8e03c52f444e723f',1,1,'VAR1'
,'{ "track_A" : "Done", "track_B" : "Done", "track_C" : "To Do" }'),
('e04c0ae4709340cb8e03c52f444e723f',1,1,'VAR2'
, '{ "track_A" : "To Do", "track_B" : "Done", "track_C" : "To Do" }')
,('849d1e6a0c2b4530a2b550829df94556',0,1,'VAR3'
,'{ "track_A" : "Done", "track_B" : "To Do", "track_C" : "To Do" }');

Query:

WITH cte AS
(
   SELECT student, "group", subgroup, k
     ,COUNT(CASE WHEN v='Done'  THEN 1 END) AS Done
     ,COUNT(CASE WHEN v='To Do' THEN 1 END) AS To_do
   FROM tab
   ,LATERAL json_each_text(status) s(k,v)
   GROUP BY student, "group", subgroup, k  
), cte2 AS
(
  SELECT student, "group", subgroup, k, json_object_agg(s.status, s.cnt) AS j
  FROM cte
  ,LATERAL (VALUES('Done', Done),('To Do', To_Do)) AS s(status, cnt)
  GROUP BY student, "group", subgroup, k   
)
SELECT student, "group", subgroup
      ,json_object_agg(k, j) AS totals
FROM cte2
GROUP BY student, "group", subgroup;

Output:

#1