amazon-redshiftamazon-redshift-spectrum

fetch key and value from a super field in redshift

I have a super field that holds JSON formatted data -

** { "awsRegion": "us-west-2", "dynamodb": { "ApproximateCreationDateTime": 1712584702997808, "Keys": { "prospectId": { "S": "string_1" } }, "NewImage": { "prospectName": { "S": "perform-test_Ram" }, "gaSessionId": { "S": "1980117787.1628661599" } }, "SizeBytes": 467, "ApproximateCreationDateTimePrecision": "MICROSECOND" }, "eventSource": "aws:dynamodb" } **

I want to extract the JSON value string prospectId,prospectName,gaSessionId I tried converting this field to varchar and the result is empty. I also tried converting to JSON_serialize and then to varchar and still the results are blank. Any help on how to fix this?

Solution

  • If this column (col) is of type super you just need to navigate to the field you want using '.' notation. Like the below query but since you have uppercase field names you need to set Redshift to case sensitive as well:

    SET enable_case_sensitive_identifier TO true;

SELECT col.dynamodb."Keys"."prospectId"."S" as prospectid
FROM <table>;

    Note the use of double quotes when an uppercase character is in a column / field identifier.