PostgreSQL 9.6将多单行列查询结果转为指定格式数组

我来帮你搞定这个需求！你想要把多个单行查询的结果转换成["ColA",8],["ColB",20],["ColC",13]这种格式的JSON数组，之前用row_to_json和array_to_json没成功，核心原因是这些函数默认生成的是JSON对象或对象数组，而我们需要直接构建键值对数组。下面分两种场景给你解决方案：

一、简单场景（对应你举的select 8 as "ColA"示例） #

如果是零散的单行查询，我们可以用json_build_array直接生成单个键值对数组，再通过array_agg聚合后转成最终的JSON数组：

SELECT array_to_json(array_agg(json_build_array(col_name, col_value)))
FROM (
  SELECT 'ColA' AS col_name, 8 AS col_value
  UNION ALL
  SELECT 'ColB' AS col_name, 20 AS col_value
  UNION ALL
  SELECT 'ColC' AS col_name, 13 AS col_value
) AS sub_query;

执行后会直接输出你要的结果：[["ColA",8],["ColB",20],["ColC",13]]

二、你的实际业务场景（按JobTitle分组的时间段统计） #

针对你提供的复杂查询，我给你两种优化方案：

方案1：直接生成键值对数组（适合少量时间段） #

每个时间段的统计直接生成["时间段", 统计值]的数组元素，再按JobTitle聚合：

SELECT 
  "JobTitle" AS "name",
  array_to_json(array_agg(time_effect)) AS "data"
FROM (
  -- 00:00时间段统计
  SELECT 
    "JobTitle",
    json_build_array('00:00', SUM(CASE WHEN "StartDateTime" < (_Date_For + '1 hour'::interval) AND "EndDateTime" > (_Date_For) THEN "Effect" ELSE 0 END)) AS time_effect
  FROM "tmpDashboardData"
  GROUP BY "JobTitle"
  
  UNION ALL
  
  -- 10:00时间段统计
  SELECT 
    "JobTitle",
    json_build_array('10:00', SUM(CASE WHEN "StartDateTime" < (_Date_For + '11 hour'::interval) AND "EndDateTime" > (_Date_For + '10 hour'::interval) THEN "Effect" ELSE 0 END)) AS time_effect
  FROM "tmpDashboardData"
  GROUP BY "JobTitle"
  
  UNION ALL
  
  -- 11:00时间段统计
  SELECT 
    "JobTitle",
    json_build_array('11:00', SUM(CASE WHEN "StartDateTime" < (_Date_For + '12 hour'::interval) AND "EndDateTime" > (_Date_For + '11 hour'::interval) THEN "Effect" ELSE 0 END)) AS time_effect
  FROM "tmpDashboardData"
  GROUP BY "JobTitle"
) AS sub
GROUP BY "JobTitle";

方案2：行转键值对（适合大量时间段，写法更简洁） #

先把所有时间段的统计结果生成一行多列的结构，再转成JSON对象后拆分成键值对，最后聚合为目标数组：

SELECT 
  "JobTitle" AS "name",
  array_to_json(array_agg(json_build_array(key, value::numeric))) AS "data"
FROM (
  SELECT 
    "JobTitle",
    json_each_text(row_to_json(t)) AS (key, value)
  FROM (
    -- 一次性生成所有时间段的统计列
    SELECT 
      "JobTitle",
      SUM(CASE WHEN "StartDateTime" < (_Date_For + '1 hour'::interval) AND "EndDateTime" > (_Date_For) THEN "Effect" ELSE 0 END) AS "00:00",
      SUM(CASE WHEN "StartDateTime" < (_Date_For + '11 hour'::interval) AND "EndDateTime" > (_Date_For + '10 hour'::interval) THEN "Effect" ELSE 0 END) AS "10:00",
      SUM(CASE WHEN "StartDateTime" < (_Date_For + '12 hour'::interval) AND "EndDateTime" > (_Date_For + '11 hour'::interval) THEN "Effect" ELSE 0 END) AS "11:00"
    FROM "tmpDashboardData"
    GROUP BY "JobTitle"
  ) AS t
) AS sub
GROUP BY "JobTitle";

这两种方案都能生成你需要的["00:00", 值], ["10:00", 值], ...格式的JSON数组，你可以根据自己的时间段数量选择更合适的写法。

内容的提问来源于stack exchange，提问作者Alan Ferneyhough