How to count instances inside json object array?

Question

I have some table that includes an array of jsonb objects as a column:

| event_id | attendees                                                                                                                                    |
|----------|----------------------------------------------------------------------------------------------------------------------------------------------|
|        1 | [{"name": "john smith", "username": "jsmith"}, {"name": "jeff jones", "username": "jjones"}, {"name": "steve woods", "username": "swoods"}] |
|        2 | [{"name": "al williams", "username": "awilliams"}, {"name": "james lee", "username": "jlee"}, {"name": "bob thomas", "username": "bthomas"}] |
|        3 | [{"name": "doug hanes", "username": "dhanes"}, {"name": "stan peters", "username": "speters"}, {"name": "jane kay", "username": "jkay"}] |

I would like to get the count of all attendees whose username matches some condition (let's say whose username starts with "j") for each event.

Looking at the documentation, I couldn't really find anything that I could use for jsonb object arrays. The closest thing I could see was the jsonb_array_elements function, but that returns a set and not individual values. so something like:

select event_id, count(jsonb_array_elements(attendees) ->> 'username') 
from events
where jsonb_array_elements(attendees) ->> 'username' like 'a%'
group by event_id

would obviously not work. Is there something that would return this output (count of usernames that begin with j for each event):

| event_id | count |
|----------|-------|
|        1 |     2 |
|        2 |     1 |
|        3 |     1 |

Answer 1

Well, just split your SQL logic to two part.

As below, you can get the all username for each event_id,

select
    event_id,
    jsonb_array_elements(attendees) ->> 'username' as user_name
from
    events;
 event_id | user_name 
----------+-----------
        1 | jsmith
        1 | jjones
        1 | swoods
        2 | awilliams
        2 | jlee
        2 | bthomas
        3 | dhanes
        3 | speters
        3 | jkay
(9 rows)

And then we can calculate some statistics data of json elements for event_id dimension,for example, you want to get the username's number of each event_id whose username started with some character such as 'j', the complete SQL would be:

with tmp as (
select
    event_id,
    jsonb_array_elements(attendees) ->> 'username' as user_name
from
    events
)
select
    event_id,
    count(1)
from
    tmp
where
    user_name like 'j%'
group by
    event_id
order by
    event_id;
 event_id | count 
----------+-------
        1 |     2
        2 |     1
        3 |     1
(3 rows)