You need to enable JavaScript to run this app. PostgreSQL如何查询JSON字段实现键为列名、值为列内容
优惠活动
大模型
产品
解决方案
定价
更多

PostgreSQL如何查询JSON字段实现键为列名、值为列内容

实现方案

首先默认fields列为性能更优的JSONB类型(如果是JSON类型,把下文涉及jsonb_开头的函数替换为json_即可,取值操作符通用):

方案1:调研问题固定(推荐)

如果所有调研的顶层键是固定已知的,直接用PostgreSQL原生JSON取值操作符提取即可,SQL语句如下:

SELECT
  fields ->> 'Monthly_household_income' AS "Monthly_household_income",
  fields ->> 'Survey_consent' AS "Survey_consent",
  fields ->> 'Are_you_working' AS "Are you working",
  fields ->> 'Network_Provider' AS "Network provider",
  fields ->> 'Do_you_own_a_phone' AS "Do you own a phone",
  fields ->> 'Whatsapp_consent' AS "Whatsapp consent",
  fields ->> 'Highest_education' AS "Highest education",
  fields ->> 'Preferred_channel' AS "Preferred channel",
  fields ->> 'Identification_type' AS "Identification type",
  fields ->> '7d91a89f-6cf7-41e3-817e-718caab32b38' AS "Phone number",
  fields ->> 'e26acde6-87b3-43eb-bc8d-68df3747e206' AS "Marital status",
  fields ->> 'What_would_make_you_stay_on_whatsapp' AS "What would make you stay on whatsapp",
  fields ->> 'Why_did_you_switch_from_wa_to_sms' AS "Why did you switch from wa to sms",
  fields ->> 'How_do_you_charge_your_phone' AS "How do you charge your phone",
  fields ->> 'Prebirth_messaging' AS "Prebirth messaging",
  fields ->> 'Completed_research_survey' AS "Completed research survey",
  fields ->> 'Estimated_due_date' AS "Estimated due date",
  fields ->> 'Started_research_survey' AS "Started research survey",
  fields ->> 'Whatsapp_undelivered_timestamp' AS "Whatsapp undelivered timestamp",
  fields ->> 'Info_consent' AS "Info consent",
  fields ->> 'How_much_airtime_do_you_use_for_momconnect_on_whatsapp' AS "How much airtime do you use for momconnect on whatsapp",
  fields ->> 'Facility_code' AS "Facility code",
  fields ->> 'What_type_of_house_do_you_live_in' AS "What type of house do you live in",
  fields ->> 'ID_number' AS "ID number",
  fields ->> 'Income_from_grants' AS "Income from grants",
  fields ->> 'Date_of_birth' AS "Date of birth",
  fields ->> 'Messaging_consent' AS "Messaging consent",
  fields ->> 'Research_consent' AS "Research consent",
  fields ->> 'Registration_date' AS "Registration date"
FROM contacts_contact;

说明:->>操作符会直接返回JSON对应路径的文本值,AS后可以自定义列展示名,比如把下划线替换为空格。如果contacts_contact是数据库名而非表名,需要先切换到该数据库再执行上述查询。


方案2:调研问题不固定(动态生成列)

如果调研问题的键会动态新增,需要自动识别所有键生成列,需要先启用tablefunc扩展,再通过动态SQL实现:

  1. 先启用扩展:
CREATE EXTENSION IF NOT EXISTS tablefunc;
  1. 执行动态生成查询的语句:
DO $$
DECLARE
    columns_sql text;
    query_sql text;
BEGIN
    -- 自动获取所有不同的顶层键,拼接查询列部分
    SELECT string_agg(
        format('fields ->> %L AS %I', key, replace(key, '_', ' ')),
        ', ' ORDER BY key
    ) INTO columns_sql
    FROM (
        SELECT DISTINCT jsonb_object_keys(fields) AS key
        FROM contacts_contact
    ) AS distinct_keys;

    -- 拼接完整SQL并执行
    query_sql := format('SELECT %s FROM contacts_contact;', columns_sql);
    RAISE NOTICE '生成的查询语句:%', query_sql;
    EXECUTE query_sql;
END $$;

说明:可以把输出的生成语句保存为视图,后续直接查询视图即可得到最新的全列数据。

内容的提问来源于stack exchange,提问作者user2875230

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.19 01:54:07