如何在PostgreSQL中对jsonb列分组以模拟EAV计数表？

要处理JSONB列中混合数组和普通值的统计需求，需要先将数组元素展开为单行，再按属性-值分组计数。以下是具体实现：

核心SQL查询 #

SELECT
    attr.key AS attribute,
    val.value AS value,
    COUNT(*) AS count
FROM
    document_template_facets,
    jsonb_each(Data) AS attr(key, json_val),
    LATERAL (
        SELECT jsonb_array_elements_text(attr.json_val) AS value
        WHERE jsonb_typeof(attr.json_val) = 'array'
        UNION ALL
        SELECT attr.json_val::text AS value
        WHERE jsonb_typeof(attr.json_val) <> 'array'
    ) AS val
GROUP BY
    attribute,
    value
ORDER BY
    attribute,
    count DESC;

代码说明 #

1. jsonb_each(Data)：遍历JSONB列的每个键值对，得到属性名（key）和对应的JSON值（json_val）。
2. LATERAL子查询：
   • 当json_val是数组类型时，用jsonb_array_elements_text将数组拆分为单个文本元素；
   • 当json_val是非数组类型（如字符串、数字）时，直接转换为文本类型。
3. 分组计数：按属性名和展开后的值分组，统计每组的出现次数。

针对示例数据的结果 #

用你提供的示例数据执行上述查询，会得到类似EAV表的统计结果：

注意事项 #

• 确保Data列是JSONB类型，如果是JSON类型，只需将函数替换为json_each和json_array_elements_text即可。
• 如果JSON值中有数字类型（如zipCode），转换为文本不会影响统计，若需要保留数字类型，可调整val.value的转换逻辑。

内容的提问来源于stack exchange，提问作者Garuuk