Split column with Json values into multiple columns in Postgres

user_13391350 0
Viewed 777

Calling DBeaver table columns:

Column Name                    #      Data type         Encoding     
created_at                     1      timestamp            az64
column_with_json_values       22      varchar(2048)        lzo

.

select column_with_json_values from table_name

column_with_json_values

{"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}

An interesting point to note is that not all of the rows have the same features. For instance, row#1 to row#5 start with messaging, row #6 start with square, row #7 start with reporting.

I would like to split this one column into multiple columns, where each column has only 1 feature.

Expected Output:

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 sql
1 Answers

This is an example query on how to access Json properties, you can cast to JSONB and then access by using ->.

Some info on -> and ->>:

PostgreSQL provides two native operators -> and ->> to help you query JSON data.

The operator -> returns JSON object field as JSON. The operator ->> returns JSON object field as text.

with jsonvalue as (
 select '{"messaging": true, "newsletters": true, "ranking_coach": true, "sms_reminders": true, "birthday_reminders": true, "merchant_shiftplan": true}'::jsonb as jsonvalues
)

select 
        jsonvalues -> 'messaging',
        jsonvalues -> 'newsletters'
                    from jsonvalue;

this will result in your expected output if you adjust my example query on your case. Hope this helped.

user_117923470
Related