如何计算JSON对象数组中的实例数？

Question

我有一张表格，其中包含一个jsonb对象数组作为一列：

| 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"}] |

我想要针对每个事件获取符合特定条件的所有参与者的数量（比如他们的用户名以“j”开头）。

查看文档后，我并没有找到能用于jsonb对象数组的函数。最接近的是jsonb_array_elements函数，但它返回一个集合而不是单个值。所以类似这样的：

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

显然不能这样做。有没有什么方法可以返回这个输出（每个事件以j开头的用户名计数）:

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

- hov

Answer 1

好的，只需将您的SQL逻辑拆分为两部分。

如下所示，您可以获取每个event_id的所有username，

最初的回答：

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)

然后我们可以计算一些与event_id维度相关的JSON元素的统计数据，例如，您想要获取以某个字符（如'j'）开头的每个event_id的username数量，完整的SQL语句如下：

最初的回答

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)

- Shawn.X