Question 90829 in PostgreSQL Question posted in
The official documentation can be found here.

Count number of occurrences of value in JSON fields with pattern stored in JSON Column (Postgres) – Postgresql

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;

enter image description here

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search