Jsonb数据postgresql计数非重复值

kuarbcqp 于 2023-01-17 发布在 PostgreSQL

关注(0)|答案(1)|浏览(179)

我有PostgreSQL 11.5与类似的jsonb数据：

[{"name":"$.publishedTitle", "value":"Code"},{"name":"$.publishedYear","value":"1972"}]
[{"name":"$.publishedTitle", "value":"Test"},{"name":"$.publishedYear","value":"2020"}]
[{"name":"$.publishedTitle", "value":"Code"},{"name":"$.publishedYear","value":"2019"}]

我想要的结果是（一个标题发布了多少次
| 标题|发布年份计数|
| - ------|- ------|
| 代码|第二章|
| 试验|1个|
我已经试过了：

SELECT distinct(b.field_value) AS publishedYearCount, COUNT(*)
  FROM (SELECT *
          FROM (SELECT (jsonb_array_elements(result) ::jsonb) - >> 'name' field_name,
                       (jsonb_array_elements(result) ::jsonb) - >> 'value' field_value,
                  FROM books
                 WHERE bookstore_id = '3') a
         WHERE a.field_name in ('$.publishedTitle', '$.publishedYear')) b
 GROUP BY b.field_value

postgresql

来源：https://stackoverflow.com/questions/75099670/jsonb-data-postgresql-count-distinct-values

1条答案

按热度按时间

7eumitmz1#

我知道你想计算每个出版物的不同年份。
您可以在一个横向连接中取消嵌套每个数组，并将年份和标题放在同一行上;那么我们就可以在外部查询中进行聚合和计数：

select x.ptitle, count(distinct x.pyear) cnt_years
from books b
cross join lateral (
    select 
        max(j.elt ->> 'value') filter(where j.elt ->> 'name' = '$.publishedTitle') ptitle,
        max(j.elt ->> 'value') filter(where j.elt ->> 'name' = '$.publishedYear' ) pyear,
    from jsonb_array_elements(b.result) j(elt)
) x
where b.bookstore_id = '3'
group by x.ptitle

赞(0）回复(0）举报 2023-01-17

我来回答

Jsonb数据postgresql计数非重复值

1条答案

相关问题

热门标签

最新问答