PostgreSQL 9.6中如何将JSON数组转换为工作日列？

嘿，针对你在PostgreSQL 9.6里处理JSON工作日字段的需求，我整理了两种实用的方法，都能帮你把数组展开成想要的列结构：

方法1：直接条件判断（简单快捷） #

这种方法利用PostgreSQL 9.6支持的jsonb包含操作符，直接判断每个工作日是否存在于数组中，写法简单，适合固定工作日范围的场景。

假设你的表名为your_table，存储JSON的列名为recurrence_data，执行以下SQL：

SELECT
  -- 生成ROW-序号，如果表有主键可以直接用主键替代ROW_NUMBER()
  'ROW-' || ROW_NUMBER() OVER () AS "ROW-",
  CASE WHEN (recurrence_data::jsonb) -> 'weekdays' @> '["1"]' THEN 'Y' ELSE 'N' END AS MON,
  CASE WHEN (recurrence_data::jsonb) -> 'weekdays' @> '["2"]' THEN 'Y' ELSE 'N' END AS TUE,
  CASE WHEN (recurrence_data::jsonb) -> 'weekdays' @> '["3"]' THEN 'Y' ELSE 'N' END AS WED,
  CASE WHEN (recurrence_data::jsonb) -> 'weekdays' @> '["4"]' THEN 'Y' ELSE 'N' END AS THU,
  CASE WHEN (recurrence_data::jsonb) -> 'weekdays' @> '["5"]' THEN 'Y' ELSE 'N' END AS FRI
FROM your_table;

说明： #

• 我们把原JSON列转成jsonb类型，用@>操作符检查数组是否包含指定工作日的数字（1=周一、2=周二，以此类推）
• 用CASE WHEN返回Y（存在）或N（不存在），你也可以换成1/0或者布尔值TRUE/FALSE
• ROW_NUMBER()用来生成你需要的ROW-序号，如果表本身有主键（比如id），直接用'ROW-' || id会更准确

运行后就能得到你想要的结构：

方法2：交叉表转换（扩展性更强） #

如果以后可能需要扩展工作日范围（比如加周六周日），这种方法更灵活。它先把数组拆成行，再通过交叉表转成列，需要先启用tablefunc扩展。

步骤1：启用tablefunc扩展 #

CREATE EXTENSION IF NOT EXISTS tablefunc;

步骤2：执行转换SQL #

WITH expanded_days AS (
  SELECT
    ROW_NUMBER() OVER () AS row_num,
    (json_array_elements_text(recurrence_data -> 'weekdays'))::int AS weekday
  FROM your_table
),
weekday_labels AS (
  SELECT 1 AS weekday, 'MON' AS label UNION ALL
  SELECT 2 AS weekday, 'TUE' AS label UNION ALL
  SELECT 3 AS weekday, 'WED' AS label UNION ALL
  SELECT 4 AS weekday, 'THU' AS label UNION ALL
  SELECT 5 AS weekday, 'FRI' AS label
)
SELECT
  'ROW-' || ed.row_num AS "ROW-",
  MAX(CASE WHEN wl.label = 'MON' THEN 'Y' ELSE 'N' END) AS MON,
  MAX(CASE WHEN wl.label = 'TUE' THEN 'Y' ELSE 'N' END) AS TUE,
  MAX(CASE WHEN wl.label = 'WED' THEN 'Y' ELSE 'N' END) AS WED,
  MAX(CASE WHEN wl.label = 'THU' THEN 'Y' ELSE 'N' END) AS THU,
  MAX(CASE WHEN wl.label = 'FRI' THEN 'Y' ELSE 'N' END) AS FRI
FROM expanded_days ed
RIGHT JOIN weekday_labels wl ON ed.weekday = wl.weekday
GROUP BY ed.row_num
ORDER BY ed.row_num;

说明： #

• expanded_days CTE把每个JSON里的weekdays数组拆成单独的行，每个工作日对应一行
• weekday_labels CTE定义了数字和工作日名称的映射，以后要加新的工作日，只需要在这里添加行即可
• 通过RIGHT JOIN保证所有工作日列都能显示，再用MAX(CASE WHEN...)把行转成列

额外建议 #

如果你的JSON列操作比较频繁，建议把它转换成jsonb类型，性能会更好：

ALTER TABLE your_table ALTER COLUMN recurrence_data TYPE jsonb USING recurrence_data::jsonb;

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