如何在PostgreSQL中统计JSON数组所有键？关联表单响应表场景问询

嘿，针对你描述的表单数据统计需求，我来一步步教你怎么实现。先把你的两张表结构标准化下，方便后续写SQL示例：

表结构说明 #

• 表单配置表（假设叫forms）：

  CREATE TABLE forms (
      form_id INT PRIMARY KEY,
      form_fields TEXT[]  -- 存储表单字段的数组，比如'{"abc","def"}'
  );
  

• 表单响应表（假设叫form_responses）：

  CREATE TABLE form_responses (
      device_id INT,
      form_id INT REFERENCES forms(form_id),
      response JSON  -- 推荐用JSONB类型，查询性能更好
  );
  

方法1：统计所有响应中出现的JSON键及提交次数 #

如果只是想从响应表的response字段里提取所有出现过的键，并统计每个键的提交次数，可以用PostgreSQL的JSON处理函数来展开键，再分组计数：

-- 针对JSON类型的response字段
SELECT
    json_object_keys(response) AS key_name,
    COUNT(*) AS occurrence_count
FROM form_responses
WHERE form_id = 1  -- 可选：仅统计指定表单的响应
GROUP BY key_name
ORDER BY occurrence_count DESC;

如果你的response字段是JSONB类型（更推荐的存储格式），把json_object_keys换成jsonb_object_keys即可：

-- 针对JSONB类型的response字段
SELECT
    jsonb_object_keys(response) AS key_name,
    COUNT(*) AS occurrence_count
FROM form_responses
WHERE form_id = 1
GROUP BY key_name
ORDER BY occurrence_count DESC;

用你的示例数据跑这个查询，结果会是：

key_name | occurrence_count
---------|------------------
def      | 2
abc      | 1

方法2：关联表单表，统计所有表单字段的提交情况 #

如果你想结合表单配置表的form_fields数组，统计所有表单字段的提交次数（包括从未被提交过的字段），可以用unnest展开表单字段数组，再左关联响应数据：

SELECT
    f.field AS form_field,
    COALESCE(COUNT(fr.response), 0) AS submission_count
FROM forms
CROSS JOIN unnest(form_fields) AS f(field)
LEFT JOIN form_responses fr
    ON fr.form_id = forms.form_id
    AND json_object_keys(fr.response) = f.field
WHERE forms.form_id = 1
GROUP BY f.field
ORDER BY submission_count DESC;

这个查询会把表单里的所有字段都列出来，哪怕某个字段没有任何提交记录（比如表单里有个ghi字段但没人提交，它的submission_count会显示0）。

额外提示 #

• 如果你的response字段里存在包含多个键的JSON对象（比如{abc:true, def:false}），上面的json_object_keys会自动把每个键拆成单独的行，统计依然准确。
• 数据量较大时，强烈建议把response字段改成JSONB类型，它支持索引，能大幅提升查询效率。

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