PostgreSQL：统计JSON列指定模式字段中'alternative'的出现次数

一、修复字段返回NULL的异常 #

从查询结果可见，telework列存储的是被双引号包裹的JSON字符串，而非直接的JSON对象。直接使用->操作符时，PostgreSQL会将其视为字符串类型处理，无法识别内部的JSON键值对，因此返回NULL。

修复后的取值查询：

select 
  telework, 
  (telework::text)::jsonb->'biweeklyWeek1-locationMon' 
from ets.agreement_t where id = 24763;

若你的PostgreSQL版本是14及以上，可使用更简洁的专用函数：

select 
  telework, 
  jsonb_parse_text(telework)->'biweeklyWeek1-locationMon' 
from ets.agreement_t where id = 24763;

二、统计每周"alternative"的出现次数 #

通过jsonb_each_text展开JSON的键值对，结合过滤与聚合统计，将结果作为独立字段嵌入主查询：

select 
  a.id, 
  a.name,
  -- 统计biweeklyWeek1-location*中值为alternative的次数
  (select count(*) 
   from jsonb_each_text((a.telework::text)::jsonb) as kv(key, val)
   where kv.key like 'biweeklyWeek1-location%'
     and kv.val = 'alternative'
     and kv.val is not null
     and kv.val != '') as alternativePerWeek1,
  -- 统计biweeklyWeek2-location*中值为alternative的次数
  (select count(*) 
   from jsonb_each_text((a.telework::text)::jsonb) as kv(key, val)
   where kv.key like 'biweeklyWeek2-location%'
     and kv.val = 'alternative'
     and kv.val is not null
     and kv.val != '') as alternativePerWeek2,
  -- 其他需要查询的字段
  ...
from ets.agreement_t a;

说明：如果telework列本身是正确的JSONB/JSON类型（无额外引号包裹），可将(a.telework::text)::jsonb替换为a.telework。过滤条件排除了NULL、空字符串的情况，适配字段可能的各种状态。

内容的提问来源于stack exchange，提问作者gene b.