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实现:
- 先启用扩展:
CREATE EXTENSION IF NOT EXISTS tablefunc;
- 执行动态生成查询的语句:
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
相关产品推荐
相关产品推荐

