Split text and digits by delimiter using regexp splitpart

From the table below

customer_id     config_json
a5  {"required_capacity": true, "setup_wizard_completed": true, "cancelation_time_minutes": 0, "display_no_preference_in_booking": true}
b16 {"required_capacity": null, "setup_wizard_completed": true, "cancelation_time_minutes": 0, "display_no_preference_in_booking": true}
        {"required_capacity": true, "cancelation_time_minutes": 0, "display_no_preference_in_booking": true}

I would like to split the config_json column into 2 columns as below

customer_id     name                   value
a5             required_capacity       true
a5             setup_wizard_completed  true 
...
b16            required_capacity       null

The regex part of the code currently looks like this:

REGEXP_REPLACE(split_part(mp.config_json,', ',s.id),'[{}": ]|(true|false||null)','')

However, there are some remains such as below:

customer_id  name                          value
a5          cancelation_time_minutes0 
b16         custom_sender_idadsd     

Thus, the formula above still needs to split by not just true,false or null, but also by digits or string. How can I fix that part of the code?

Answer

You can use json_each():

select t.customer_id, config.key as name, config.value
from t cross join lateral
     json_each(t.config_json::json) config;

Here is a db<>fiddle.