使用json_each()取消嵌套JSON后，可以使用标量子查询

select ... other columns ...,
       (select count(*)
        from json_each_text(s.telework) as x(key,value)
        where x.key like 'biweeklyWeek1-location%'
          and x.value = 'alternative') as week1_alternative_count,
       (select count(*)
        from json_each_text(s.telework) as x(key,value)
        where x.key like 'biweeklyWeek2-location%'
          and x.value = 'alternative') as week2_alternative_count
from some_schedule s

顺便说一句：存储JSON的推荐数据类型是jsonb

假设：远程工作JSON列包含一个（键，值）对列表，键的格式为biweekly?-location???。
1.使用json_each（）和横向连接将JSON列拆分为每行的（key，value）对。
1.提取前缀和聚合，如下所示：

select s.id,
        s.name,
        substring(t.key,1,22)  as week_location,
        sum(case when t.value::text = '"alternative"' then 1 else 0 end) as  alternative_count
   from some_schedule s,
   lateral json_each(s.telework) t
   group by 1,2,3;

输出如下所示：

id |name |week_location         |alternative_count|
---+-----+----------------------+-----------------+
101|david|biweeklyWeek1-location|                4|
101|david|biweeklyWeek2-location|                4|

编辑：根据注解，json_each（）也可用于SELECT

select id,
       name,
       telework,
       json_each(telework) as key_value
  from some_schedule;

输出量：

id |name |key_value                                    |
---+-----+---------------------------------------------+
101|david|(biweeklyWeek1-locationMon,"""alternative""")|
101|david|(biweeklyWeek1-locationTue,"""agency""")     |
101|david|(biweeklyWeek1-locationWed,"""alternative""")|
101|david|(biweeklyWeek1-locationThu,"""alternative""")|
101|david|(biweeklyWeek1-locationFri,"""alternative""")|
101|david|(biweeklyWeek2-locationMon,"""alternative""")|
101|david|(biweeklyWeek2-locationTue,"""agency""")     |
101|david|(biweeklyWeek2-locationWed,"""alternative""")|
101|david|(biweeklyWeek2-locationThu,"""alternative""")|
101|david|(biweeklyWeek2-locationFri,"""alternative""")|

您还可以解析key_value列以获取所需内容