CODEWITHSUNDEEP
sqlsplitamazon-redshift
Luc
Luc

Reputation: 747

Split string by comma delimiter in Redshift

I want to transform the column below

select config_json from table 

   {"required_capacity": true, "cancelation_time_minutes": 0, "display_no_preference_in_booking": true}
{"feedback": true, "required_capacity": true, "setup_wizard_completed": false, "cancelation_time_minutes": 0, "display_no_preference_in_booking": true}
{"required_capacity": false, "cancelation_time_minutes": 0, "display_no_preference_in_booking": true}
{"required_capacity": true, "setup_wizard_completed": true, "cancelation_time_minutes": 0, "display_no_preference_in_booking": true}
{"feedback": true, "required_capacity": true, "setup_wizard_completed": false, "cancelation_time_minutes": 0, "display_no_preference_in_booking": true}

into a table format like this:

required_capacity     cancelation_time_minutes       display_no_preference_in_booking  ...
true                              0                         true                       ...
true                              0                         true                       ...
false                             0                         true                       ...

So far, I first remove the {} sign from the column, and plan to split the column by delimiter ','.

SELECT string_to_array(config_json, ',') as abcd
FROM  (select config_json, regexp_replace(config_json, '[{} ]','') as no_curly_bracket --remove the {} sign 
        from table ) a

The error message says:

 Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;

Upvotes: 0

Views: 2024

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Redshift doesn't support arrays or table valued functions. You can extract each column separately:

select regexp_substr(config_json, '"required_capacity": ([^,]+)', 1, 1, 'e') as required_capacity,
       regexp_substr(config_json, '"cancelation_time_minutes": ([^,]+)', 1, 1, 'e') as cancelation_time_minutes,
       . . .

Upvotes: 0

Related Questions