Getting JSON Data from Redshift table
I'm still trying to figure out how to not have to list every item without composite types or arrays, but this should do you for generating json without needing to do string stuff.
CREATE OR REPLACE FUNCTION test_function (userid int, firstname varchar, lastname varchar, email varchar)RETURNS varchar(max) immutable as $$ import json o = { "userid": userid, "firstname": firstname, "lastname": lastname, "email": email } return json.dumps(o) $$ LANGUAGE plpythonu;
Depending on what you want to do, you can use one of those functions:http://docs.aws.amazon.com/redshift/latest/dg/json-functions.html
JSON_EXTRACT_PATH_TEXT
is quite useful http://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html
select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}','f4', 'f6');json_extract_path_text---------------------- star