sql - 如何计算 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”开头)的所有与会者的数量。

查看 documentation ，我真的找不到任何可以用于 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 |

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元素的统计数据，比如你想获取username每个event_id的个数 其用户名以某个字符开头，例如'j'，完整的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)