sql - 在 SQL 中将字典拆分为多列
问题描述
从 API 获取的数据返回存储在列中的字典格式,如下所示:
{"messaging": true, "newsletters": true, "ranking_coach": true, "sms_reminders": true, "birthday_reminders": true, "merchant_shiftplan": true}
{"messaging": false, "yext_sync": false, "marketplace": false, "newsletters": false, "ranking_coach": false, "sms_reminders": false, "sms_newsletters": false, "merchant_payment": true, "customer_feedback": true, "birthday_reminders": false, "merchant_dashboard": true, "merchant_shiftplan": false, "reserve_with_google": false, "sms_customer_confirmation": false, "individual_email_from_name": true, "appointment_location_customer": false}
{"messaging": false, "yext_sync": false, "marketplace": false, "newsletters": false, "ranking_coach": false, "sms_reminders": false, "sms_newsletters": false, "merchant_payment": true, "customer_feedback": true, "birthday_reminders": false, "merchant_dashboard": true, "merchant_shiftplan": false, "reserve_with_google": false, "sms_customer_confirmation": false, "individual_email_from_name": true, "appointment_location_customer": false}
{"messaging": false, "newsletters": false, "merchant_payment": false, "birthday_reminders": false, "merchant_shiftplan": false, "double_opt_in_required": false}
{"messaging": false, "yext_sync": false, "marketplace": false, "newsletters": false, "ranking_coach": false, "sms_reminders": false, "sms_newsletters": false, "merchant_payment": true, "customer_feedback": true, "birthday_reminders": false, "merchant_dashboard": true, "merchant_shiftplan": false, "reserve_with_google": false, "sms_customer_confirmation": false, "individual_email_from_name": true, "appointment_location_customer": false}
{"square": false, "insights": true, "api_token": false, "messaging": true, "wait_list": false, "yext_sync": false, "marketplace": false, "newsletters": true, "pdf_prefill": false, "free_product": false, "website_duda": false, "ranking_coach": false, "sms_reminders": true, "contact_widget": false, "online_booking": true, "sms_newsletters": false, "merchant_payment": false, "shared_customers": false, "customer_feedback": true, "external_services": false, "birthday_reminders": true, "merchant_dashboard": true, "merchant_shiftplan": false, "net_promoter_score": false, "new_booking_widget": true, "reserve_with_google": false, "file_download_widget": false, "automated_newsletters": true, "double_opt_in_required": false, "sms_customer_confirmation": false, "individual_email_from_name": false, "new_closingtime_background": false, "gdpr_marketing_opt_in_modal": false, "appointment_location_customer": false, "facebook_instagram_integration": false, "new_full_screen_booking_widget": true, "merchant_logo_on_customer_email": true, "show_all_branch_option_on_insights": false, "participating_account_notifications": false, "show_newsletter_non_subscriber_selection": false}
{"reporting": true, "newsletters": false, "merchant_payment": false, "customer_feedback": true, "reserve_with_google": true, "new_closingtime_background": true, "merchant_logo_on_customer_email": true}
{"messaging": false, "yext_sync": false, "marketplace": false, "newsletters": false, "ranking_coach": false, "sms_reminders": false, "sms_newsletters": false, "merchant_payment": true, "customer_feedback": true, "birthday_reminders": false, "merchant_dashboard": true, "merchant_shiftplan": false, "reserve_with_google": false, "sms_customer_confirmation": false, "individual_email_from_name": true, "appointment_location_customer": false}
{"messaging": false, "newsletters": false, "merchant_payment": false, "birthday_reminders": false, "merchant_shiftplan": false, "double_opt_in_required": false, "new_closingtime_background": true, "facebook_instagram_integration": true, "show_all_branch_option_on_insights": true, "show_newsletter_non_subscriber_selection": false}
{"sms_reminders": true, "customer_feedback": true, "merchant_dashboard": true, "reserve_with_google": false, "show_all_branch_option_on_insights": true}
值得注意的一点是,并非所有行都具有相同的特征。例如,第 1 行到第 5 行以消息开头,第 6 行以正方形开头,第 7 行以报告开头。
我想将这一列拆分为多列,其中每列只有 1 个功能。
预期输出:
No. messaging newsletter ranking_coach sms_reminders
1 true true true true
2 false null null null
3 false null null null
..
6 null null null null
解决方案
您可以在 postgresql 中使用 json 功能:
select
Jsoncol ->> 'messaging' messaging,
Jsoncol ->> 'newsletters' newsletters,
Jsoncol ->> 'ranking_coach' ranking_coach,
Jsoncol ->> 'sms_reminders' sms_reminders
from tablename
db<>在这里摆弄
推荐阅读
- javascript - 正则表达式替换不在锚标签内的单词
- flutter - FutureBuilder:与未来数据相关的输出
- python - 无法在 android studio 的 Interpreter 上运行 tflite 模型
- tensorflow - 多输入模型错误:找不到可以处理输入的数据适配器
- javascript - 如何在反应中从动态表中获取行数据
- mysql - 根据 MySQL 中相同计算列的先前值计算列
- django - 单模型多表:Django ORM vs SQL 查询
- spring-boot - 并行生产者和消费者使用项目反应器隐藏延迟
- r - 如何在 R 中为多个类别实施变化点分析并使用自定义 x 和 y 轴标签绘制图形?
- c# - 有条件地检查数组是否等于特定值