CODEWITHSUNDEEP
regexamazon-redshift
Carter
Carter

Reputation: 1

Redshift regexp_substr - extract data from a JSON type format

Help much appreciated - I have a field in Redshift giving data of the form:

{\"frequencyCapList\":[{\"frequencyCapped\":true,\"frequencyCapPeriodCount\":1,\"frequencyCapPeriodType\":\"DAYS\",\"frequencyCapCount\":501}]}

What I would like to do is parse this cleanly as the output of a Redshift query into some columns like:

Frequency Cap Period Count | Frequency Cap Period Type | Frequency Cap Count
1                          |           DAYS            |    501

I believe I need to use the regexp_subst function to achieve this but I cannot work out the syntax to get the required output :(

Thanks in advance for any assistance, Carter

Upvotes: 0

Views: 563

Answers (1)

Jon Scott
Jon Scott

Reputation: 4354

Here you go

select json_extract_path_text(json_extract_array_element_text(json_extract_path_text(replace('{\"frequencyCapList\":[{\"frequencyCapped\":true,\"frequencyCapPeriodCount\":1,\"frequencyCapPeriodType\":\"DAYS\",\"frequencyCapCount\":501}]}','\\',''),'frequencyCapList'),0),'frequencyCapPeriodCount');

just replace the last string with each one you want to extract!

Upvotes: 2

Related Questions