Dataset query on Redshift cluster

Question & Answer
, , , , , , ,
1

I have a table available on redshift already, I am using that data from the table to create analysis, however there is one column called “Custom Fields” which contains String data as below example:

{“site”:{“id”:“site”,“value”:“VCSCRI”,“type”:“String”},“training_manager”:{“id”:“training_manager”,“value”:“acoverst”,“type”:“String”},“type_of_training”:{“id”:“type_of_training”,“value”:“Skill-Add”,“type”:“String”},“status”:{“id”:“status”,“value”:“Planned”,“type”:“String”},“training_marketplace”:{“id”:“training_marketplace”,“value”:“US”,“type”:“String”},“language”:{“id”:“language”,“value”:“English”,“type”:“String”},“channel”:{“id”:“channel”,“value”:“All Channels”,“type”:“String”},“skill”:{“id”:“skill”,“value”:“Other”,“type”:“String”},“ou”:{“id”:“ou”,“value”:“D2AS US Gen”,“type”:“String”},“trainer”:{“id”:“trainer”,“value”:“josperez”,“type”:“String”},“type_of_trainer_”:{“id”:“type_of_trainer_”,“value”:“Permanent Trainer”,“type”:“String”},“main_trainer_”:{“id”:“main_trainer_”,“value”:“josperez”,“type”:“String”},“start_date”:{“id”:“start_date”,“value”:“2023-03-15”,“type”:“Date”,“value_tz”:“2023-03-15T18:30:00.000Z”},“end_date”:{“id”:“end_date”,“value”:“2023-03-16”,“type”:“Date”,“value_tz”:“2023-03-16T18:30:00.000Z”},“planned_hc”:{“id”:“planned_hc”,“value”:17,“type”:“Number”},“number_of_sessions”:{“id”:“number_of_sessions”,“value”:2,“type”:“Number”},“invested_trainer_hours”:{“id”:“invested_trainer_hours”,“value”:2,“type”:“Number”},“day_1_hc”:{“id”:“day_1_hc”,“value”:17,“type”:“Number”},“graduated_training_hc”:{“id”:“graduated_training_hc”,“value”:17,“type”:“Number”},“graduated_total_hc”:{“id”:“graduated_total_hc”,“value”:17,“type”:“Number”},“invested_gradbay_hours”:{“id”:“invested_gradbay_hours”,“value”:0,“type”:“Number”}}

Out of this string column, I would need specific substring data for example: “invested_trainer_hours” and its value in a particular column for analysis, similarly “graduated total hc” etc. How can I achieve this?

  1. should I create calculated field, use any specific function to extract. - I already tried few from String functions, however they did not work.
  2. Can I use direct query from data source and create custom SQL, if yes what command in the SQL would let me achieve this?
2

Hello this is solved.

I figured out that it is json, used the function - parsejson.

Thanks.

1 Like