postgres 如何把多行数据，合并一行，返回json字符串

 

 需要得格式 String json = "{"1984":"1","1985":"1"}";

?

1 2 3 4 5 6 7 8 9 10 11

SELECT     concat ( '{', A.strjson, '}' ) FROM     (     SELECT         string_agg ( concat ( '"', YEAR, '":', '"', zsxx, '"' ), ',' ) strjson     FROM         "pc_pg_his_sczzzs"     WHERE         xzqbm = '530721'     ) A

 通过 string_agg 函数 合并列得结果

最终结果：

?

1

{"1984":"1","1985":"1","1988":"1","1989":"1","1990":"1","1991":"1","1993":"1","1994":"1","1997":"1","1998":"1","2000":"1","2001":"1","2007":"1","2009":"1","2010":"1","2016":"1","2018":"1","2019":"1","1986":"1","1987":"1","1992":"1","1995":"1","1996":"1","1999":"1","2002":"1","2003":"1","2004":"1","2005":"1","2006":"1","2008":"1","2011":"1","2012":"1","2013":"1","2014":"1","2015":"1","2017":"1","2020":"1","1983":"1","1978":"1","1979":"1","1980":"1","1980":"1","1981":"1","1982":"1.1"}