sql - 将一个表的行转换为 json 并将其添加到另一个表 PostgreSQL

Question

我有两个表 progress_report 和 progress_report_activity。

CREATE TABLE PROGRESS_REPORT
(
  REPORT_ID VARCHAR NOT NULL,
  PCP_PATIENT_ID VARCHAR NOT NULL,
  VC_PRODUCT_ID VARCHAR ,
  REPORT_DURATION_DAYS INTEGER NOT NULL,
  REPORT_GENERATION_DATE BIGINT,
  IS_REPORT_READ BOOLEAN ,
  ACTIVITY_DETAILS JSONB,
  SYMPTOMS_DETAILS JSONB,
  PRIMARY KEY (REPORT_ID)
);

CREATE TABLE PROGRESS_REPORT_ACTIVITY
(
REPORT_ACTIVITY_ID VARCHAR NOT NULL,
REPORT_ID VARCHAR NOT NULL,
ACTIVITY_CONFIG_ID VARCHAR NOT NULL,
ACTIVITY_ASSISTANCE VARCHAR NOT NULL,
PRIMARY KEY (REPORT_ACTIVITY_ID)
)

我需要将 ACTIVITY_CONFIG_ID 和 ACTIVITY_ASSISTANCE 从 PROGRESS_REPORT_ACTIVITY 转换为 json，并将此 json 添加到 PROGRESS_REPORT 中的 ACTIVITY_DETAILS 列。

我尝试使用这个查询来形成 json:

SELECT ROW_TO_JSON(t) FROM (SELECT activity_config_id, activity_assistance FROM progress_report_activity pra inner join progress_report pr on pr.report_id=pra.report_id) t

但是问题 PROGRESS_REPORT_ACTIVITY 会为同一个 report_id 返回多行，这在两个表中都很常见。

示例数据:

insert into progress_report_activity values ('1','1','11','qwerty')
insert into progress_report_activity values ('3','1','13','qwertyDup')
insert into progress_report_activity values ('2','2','12','qwerty2')
insert into progress_report_activity values ('10','2','11','qwerty12223')

insert into progress_report values ('1','123','taltz',123,123456,true,null,null);
insert into progress_report values ('10','123','olumiant',123,123456,true, null,null)

预期输出:下面是两种不同的输出格式，你能为这两种格式提出查询建议吗？

1]示例 progress_report 行应如下所示:

  1,123,taltz,123,123456,true, {"activity_config_id" : ["11","13"], "activity_assistance" : ["qwerty","qwertyDup"]}

2]示例 progress_report 行:

1,123,taltz,123,123456,true, {"activity" : [{"activity_config_id":"11",
    "activity_assistance" : "qwerty"},
    {"activity_config_id":"13",
    "activity_assistance" : "qwertyDup" }]

Answer 1

9.2 is very limited with json support .解决方案看起来不太好:

t=# with p as (SELECT pr.report_id,array_agg(activity_config_id) activity_config_id, array_agg(activity_assistance) activity_assistance FROM progress_report_activity pra inner join progress_report pr on pr.report_id=pra.report_id group by pr.report_id)
select row_to_json(j), report_id from p, (select activity_config_id,activity_assistance from p) j;
                                   row_to_json                                   | report_id
---------------------------------------------------------------------------------+-----------
 {"activity_config_id":["11","13"],"activity_assistance":["qwerty","qwertyDup"]} | 1
(1 row)

现在，当您为每个 report_id 准备好 json 时，更新它:

t=# with p as (SELECT pr.report_id,array_agg(activity_config_id) activity_config_id, array_agg(activity_assistance) activity_assistance FROM progress_report_activity pra inner join progress_report pr on pr.report_id=pra.report_id group by pr.report_id)
, d as (select row_to_json(j), report_id from p, (select activity_config_id,activity_assistance from p) j)
update progress_report set ACTIVITY_DETAILS = row_to_json
from d where progress_report.report_id = d.report_id
;
UPDATE 1

更新以实现第二个预期结果我不限制为 9.2(显然 OP 有 9.6):

 with c as (select report_id,json_agg(json_build_object('activity_config_id',activity_config_id,'activity_assistance',activity_assistance)) AS j from progress_report_activity group by report_id)
update progress_report pr set ACTIVITY_DETAILS = concat('{"activity" :',j,'}')::json
from c where c.report_id = pr.report_id;

http://sqlfiddle.com/#!17/13555/2