postgresql 如何在postgres中使用jsonb列的groupBy来模拟EAV计数表？

8e2ybdfx 于 2023-02-12 发布在 PostgreSQL

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

我有一个jsonb列，如下所示：
| 身份证|数据|
| - ------|- ------|
| 1个|{州：["CA"，"NY"]，县：["洛杉矶"]}|
| 第二章|{城市：["堪萨斯城"]，邮编：一二三四五|
| 三个|{州：["CO，WA"]，邮编：五二一二|
但是我曾经有这样一个数据结构：
| 身份证|属性|价值|
| - ------|- ------|- ------|
| 1个|状态|化学文摘社|
| 1个|状态|纽约州|
| 第二章|城市|堪萨斯城|
等等。
我过去只能写一个简单的查询，如下所示：

SELECT attribute, value, count(*)
    FROM table
    GROUP BY attribute, value;

并且输出将产生：
| 属性|价值|计数|
| - ------|- ------|- ------|
| 郡|纽约县|十一|
| 城市|堪萨斯城|二十二|
| 状态|化学文摘社|十五|
| 拉链|小行星10|二十一|
| 状态|纽约州|五个|
我尝试生成与上面相同的表，但使用jsonb表，但我在获得所需的输出时遇到了麻烦。
我试过像这样使用jsonb_each_text：

with t1 as
    (select jsonb_each_text(facets) as rec from document_template_facets)
select (rec).key, sum((rec).value::int) from t1 group by (rec).key;

问题是，它不适用于我的数据中的数组类型，如城市，县等...有什么方法可以让数组在上面的查询中扁平化，以获得计数工作？

postgresql

来源：https://stackoverflow.com/questions/75406238/how-to-groupby-in-postgres-with-jsonb-column-to-mimic-an-eav-count-table

1条答案

按热度按时间

a64a0gku1#

jsonb_each_text()函数返回行，因此它必须位于查询的from部分。请使用cross join lateral执行此操作。
下面的查询返回您想要的结果。jsonb_array_elements_text中的case通过将数据中的zipCode元素转换为单元素数组来处理标量值。

with expand_keys as (
  select id, k, a
    from tab
         cross join lateral jsonb_each(data) as j(k, a)
), expand_arrays as (
  select id, k, a, el
    from expand_keys
         cross join lateral 
           jsonb_array_elements_text(
             case jsonb_typeof(a)
               when 'array' then a
               else jsonb_build_array(a)
             end
           ) as ar(el)
)
-- select * from expand_arrays; --run this, instead, to see interim results
select k as attribute, el as value, count(*) as cnt
  from expand_arrays
 group by k, el;

Fiddle here

赞(0）回复(0）举报 2023-02-12

我来回答

postgresql 如何在postgres中使用jsonb列的groupBy来模拟EAV计数表？

1条答案

相关问题

热门标签

最新问答