I have a JSON column, telework, stored in Postgres which looks like

"{ ..., 
  "biweeklyWeek1-locationMon": "alternative", 
  "biweeklyWeek1-locationTues": "agency", 
  "biweeklyWeek1-locationWeds": "alternative", 
  "biweeklyWeek1-locationThurs": "alternative", 
  "biweeklyWeek1-locationFri": "alternative", 
  ... ,
  "biweeklyWeek2-locationMon": "alternative", 
  "biweeklyWeek2-locationTues": "agency", 
  "biweeklyWeek2-locationWeds": "alternative", 
  "biweeklyWeek2-locationThurs": "alternative", 
  "biweeklyWeek2-locationFri": "alternative", 
... }"

I need to count the number of occurrences of "alternative" in the biweeklyWeek1-location* fields and biWeeklyWeek2-location* fields separately and select these two as separate fields in the main query. It's possible that the values in these fields could be filled, blank (""), or null. Also, it's possible that these fields are partially or completely missing in the JSON.

select a.id, 
       a.name,
       a.telework->>??? as alternativePerWeek1, 
       a.telework->>??? as alternativePerWeek2,
       ...

Strangely enough, even when I do the following single example with -> a hard-coded ID, I get a NULL result even though I see that it shouldn't be NULL:

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

CodePudding user response：

You can use a scalar sub-query after unnesting the JSON using json_each()

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

Btw: the recommended data type to store JSON is jsonb

CodePudding user response：

Assumption: telework JSON column contains a list of (key, value) pair and key is in the format biweekly?-location???.

1. use json_each() and lateral join to split the JSON column into (key, value) pair for each row.
2. extract the prefix and aggregate as below:

 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;

The output is like:

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

EDIT: Per comment, json_each() can also be used in SELECT

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

Output:

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""")|

You can also parse the key_value column to get what you need