Convert AWS redshiftdataapiservice.GetStatementResultOutput to JSON or structure

Question content

I have an AWS Redshift Serverless database that I am querying through the redshiftdataapiservice of the AWS Go SDK. The query and all that works, but the records are returned in a format that is difficult to use/understand.

My code is like this:

import (
    "fmt"
    "log"
    "time"
    "os"
    "context"

    "github.com/aws/aws-sdk-go-v2/aws"
    "github.com/aws/aws-sdk-go-v2/config"
    "github.com/aws/aws-sdk-go-v2/service/redshiftdata"
    "github.com/aws/aws-sdk-go-v2/service/redshiftdata/types"
)

// Execute a Redshift query and return a result statement output
func executeRedshiftQuery(sql string) (*redshiftdata.GetStatementResultOutput, error) {
    // Load the Shared AWS Configuration (~/.aws/config)
    cfg, err := config.LoadDefaultConfig(context.TODO(), config.WithRegion(os.Getenv("AWS_REGION")))
    if err != nil {
        return nil, err
    }

    // Create a service client
    redshiftclient := redshiftdata.NewFromConfig(cfg)
    execStmt, err := redshiftclient.ExecuteStatement(context.TODO(), &redshiftdata.ExecuteStatementInput{
        WorkgroupName: aws.String(os.Getenv("WG_NAME")),
        Database:      aws.String(os.Getenv("DB_NAME")),
        Sql:           aws.String(sql),
    })
    if err != nil {
        return nil, err
    }

    // wait for query to finish
    for {
        descStmt, err := redshiftclient.DescribeStatement(context.TODO(), &redshiftdata.DescribeStatementInput{
            Id: execStmt.Id,
        })
        if err != nil {
            return nil, err
        }

        // return an error if the query failed or aborted
        if descStmt.Status == types.StatusStringFailed || descStmt.Status == types.StatusStringAborted {
            err := errors.New("the Redshift query failed or was aborted")
            return nil, err
        } else if descStmt.Status != types.StatusStringFinished {
            time.Sleep(1 * time.Second)
            continue
        }

        break
    }

    // get the results
    resultStmt, err := redshiftclient.GetStatementResult(context.TODO(), &redshiftdata.GetStatementResultInput{
        Id: execStmt.Id,
    })
    if err != nil {
        return nil, err
    }

    return resultStmt, nil
}

I find that 2D arrays of *Fields are difficult to work with. How do I (preferably simply) map this to a usable JSON, or rather an array of type structs? Or is there a way to request JSON from Redshift? If possible, I'd like to keep all of this entirely within my Golang application.

Correct answer

I didn't find an official way, but the method below works by creating a mapping fragment of column names to column values ​​and then unmarshalling from there .

// Extracts the column name from column metadata for a given column index
func getColumnName(metadata []types.ColumnMetadata, index int) string {
    if index < len(metadata) {
        // We assume the metadata is in the same order as the columns in the record.
        // If the column name is not set or empty, we can fallback to a default naming convention.
        if metadata[index].Name != nil {
            return *metadata[index].Name
        }
        return fmt.Sprintf("column_%d", index)
    }
    // Fallback if the index is out of range of the metadata slice.
    return fmt.Sprintf("unknown_column_%d", index)
}

// Converts query results to JSON bytes for easy unmarshaling to structs
func queryResultsToJSON(query_results *redshiftdata.GetStatementResultOutput) ([]byte, error) {
    // Convert the records to a slice of maps for JSON conversion
    var records []map[string]interface{}

    for _, row := range query_results.Records {
        record := make(map[string]interface{})
        for idx, col := range row {
            // Use the column metadata to determine the name of the column
            columnName := getColumnName(query_results.ColumnMetadata, idx)

            // Check the type of the value and assign it to the record map
            var value interface{}
            switch v := col.(type) {
            case *types.FieldMemberBlobValue:
                value = v.Value
            case *types.FieldMemberBooleanValue:
                value = v.Value
            case *types.FieldMemberDoubleValue:
                value = v.Value
            case *types.FieldMemberIsNull:
                value = nil
            case *types.FieldMemberLongValue:
                value = v.Value
            case *types.FieldMemberStringValue:
                value = v.Value
            }
            record[columnName] = value
        }
        records = append(records, record)
    }

    // Marshal the records to JSON
    jsonBytes, err := json.Marshal(records)
    if err != nil {
        log.Error("failed to marshal records to JSON, " + err.Error())
        return nil, err
    }

    return jsonBytes, nil
}

The above is the detailed content of Convert AWS redshiftdataapiservice.GetStatementResultOutput to JSON or structure. For more information, please follow other related articles on the PHP Chinese website!