postgresql多条数据合并，并将多个字段拼接成一个json字段

在pgAdmin3中，要建立一个多表联合查询的复杂视图，查阅很多资料和实验，总算是把视图做出来了

首先，我们来看一下简单的查询结果是怎么样的。

简单的多表查询语句如下：

-- View: public."V_ZHJG_GRID"

-- DROP VIEW public."V_ZHJG_GRID";

CREATE OR REPLACE VIEW public."V_ZHJG_GRID" AS 
 SELECT a."ID",
    a."GRIDID",
    a."GRIDNAME",
    a."TYPE",
    a."XZQ",
    a."PQID",
    a."PQNAME",
    a."BBOX",
    a.the_geom,
    u."ID" AS "USERID",
    u."TRUENAME" AS "USERNAME",
    u."PHONE"
   FROM "ZHJG_GRID_ATTRIBUTE" a
     LEFT JOIN "ZHJG_GRID_USER" ue ON a."ID"::text = ue."GRIDATTRIBUTEID"::text
     LEFT JOIN "KTW_UC_USER" u ON u."ID"::text = ue."USERID"::text;

ALTER TABLE public."V_ZHJG_GRID"
  OWNER TO postgres;
COMMENT ON VIEW public."V_ZHJG_GRID"
  IS '网格属性信息';

查询结果如下：

显然这样的结果并不是我所需要的，我需要的是一条数据，将后面的人员信息组合成一个json数组格式

为满足条件，我首先要做的就是需要将后面查询出来的用户信息，拼接成一个json格式，使用concat将结果拼接成json，第二步，我需要将单个的json拼接成json数组，这样方便后面获取，同时需要把数据合并成一条。

使用string_agg合并json,concat完善json成数组，通过group by将多条数据合并成一条。

最终的sql语句如下：

-- View: public."V_ZHJG_GRIDANDUSER"

-- DROP VIEW public."V_ZHJG_GRIDANDUSER";

CREATE OR REPLACE VIEW public."V_ZHJG_GRIDANDUSER" AS 
 SELECT n."ID",
    n."GRIDID",
    n."GRIDNAME",
    n."TYPE",
    n."XZQ",
    n."PQID",
    n.gid,
    n."PQNAME",
    n."BBOX",
    n.the_geom,
    concat('[', string_agg(m."user", ','::text), ']') AS "user"
   FROM 
   ( SELECT a."ID",
         a."GRIDID",
         a."GRIDNAME",
         a."TYPE",
         a."XZQ",
         a."PQID",
         a."PQID" AS gid,
         a."PQNAME",
         a."BBOX",
         a.the_geom,
         ue."USERID"
     FROM "ZHJG_GRID_ATTRIBUTE" a
     LEFT JOIN "ZHJG_GRID_USER" ue ON a."ID"::text = ue."GRIDATTRIBUTEID"::text) n
LEFT JOIN 
   ( SELECT 
         u."ID" AS "USERIDS",
         concat('{"userId":"', u."ID", '","userName":"', u."TRUENAME", '","phone":"', 
         u."PHONE", '","orgId":"', u."ORGID", '","postId":"', p."ID", '","postName":"', 
         p."NAME", '"}') AS "user"
     FROM "KTW_UC_USER" u
     LEFT JOIN "KTW_UC_POST_USER" pu ON u."ID"::text = pu."USERID"::text
     LEFT JOIN "KTW_UC_POST" p ON pu."POSTID"::text = p."ID"::text) m 
ON n."USERID"::text = m."USERIDS"::text
GROUP BY n."ID", n."GRIDID", n."GRIDNAME", n."TYPE", n."XZQ", n."PQID", n.gid, n."PQNAME", n."BBOX", n.the_geom;

ALTER TABLE public."V_ZHJG_GRIDANDUSER"
  OWNER TO postgres;
COMMENT ON VIEW public."V_ZHJG_GRIDANDUSER"
  IS '网格人员属性视图';

查询结果如下：

user的格式：

[{"userId":"kf8be17e1dd8448218e9ac78460015d2","userName":"xcy01","phone":"18407395922","orgId":"k547db647d4444233a63421ecc865495","postId":"kf0322b1b35854e7ab43af4c70a083cf","postName":"lwlk巡查员"},{"userId":"k4ce2af0d26ec42a09e403807eb32932","userName":"kcy01","phone":"18407395922","orgId":"k547db647d4444233a63421ecc865495","postId":"k40030a9ae16741ac88ab6a2d3cb02c6","postName":"lwlk勘测员"},{"userId":"ked42694b95c8443caaa2d723faa591d","userName":"责任人","phone":"18407395922","orgId":"k547db647d4444233a63421ecc865495","postId":"kace4ee2e1ca04ad2a94a7dca168f2c5","postName":"lwlk责任人"},{"userId":"ka418df5dbb9d4af48a0b17a437577e3","userName":"kcy02","phone":"18407395922","orgId":"k547db647d4444233a63421ecc865495","postId":"k40030a9ae16741ac88ab6a2d3cb02c6","postName":"lwlk勘测员"}]

好了，想要的结果也出来了，过程还是比较艰辛的，哈哈，记录下来，防止后面忘记了