如何拆分PostgreSQL jsonb列单行数据生成与物理表一致的多行查询结果

调整后查询语句 #

SELECT
  row_number() OVER () AS "LOS",
  t."SpeedLessThan",
  t."SpeedAtLeast"
FROM "ProjectMqs"
CROSS JOIN LATERAL unnest(
  -- 三个等级对应的SpeedLessThan值，最后一行用1000覆盖所有>999的场景
  ARRAY[
    ("LevelOfServiceConfig"->>'Danger')::numeric,
    ("LevelOfServiceConfig"->>'TrafficJam')::numeric,
    1000
  ],
  -- 三个等级对应的SpeedAtLeast值，第一行用0过滤<0的场景
  ARRAY[
    0,
    ("LevelOfServiceConfig"->>'Danger')::numeric,
    ("LevelOfServiceConfig"->>'TrafficJam')::numeric
  ]
) AS t("SpeedLessThan", "SpeedAtLeast")
WHERE "ProjectId" = 500
  AND NULLIF(regexp_replace("Name", '\D','','g'), '')::numeric = 1;

逻辑说明 #

• 通过CROSS JOIN LATERAL + unnest的组合，将单行jsonb中的阈值和手动补充的边界值拼接为3组区间数值，拆分为3行输出
• 输出的三个区间依次为：0 ≤ 速度 < Danger阈值、Danger阈值 ≤ 速度 < TrafficJam阈值、TrafficJam阈值 ≤ 速度 < 1000，刚好满足小于0的数值不命中任何区间、大于等于999的数值全部命中最后一行的要求
• row_number() OVER ()生成连续自增的LOS序号，输出字段与原物理表查询完全一致
• 如果JSON中存储的阈值字段名、等级优先级和示例不符，直接调整unnest两个数组内的取值顺序即可

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