将 AWS redshiftdataapiservice.GetStatementResultOutput 转换为 JSON 或结构

问题内容

我有一个 AWS Redshift Serverless 数据库，我正在通过 AWS Go SDK 的 redshiftdataapiservice 查询该数据库。查询和所有这些都有效，但记录以难以使用/理解的格式返回。

我的代码是这样的：

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
}

我发现 *Fields 的二维数组很难处理。我如何（最好是简单地）将其映射到可用的 JSON，或者说类型结构数组？或者有没有办法从 Redshift 请求 JSON？如果可能的话，我想将所有这些完全保留在我的 Golang 应用程序中。

正确答案

我没有找到官方方法，但下面的方法是通过创建列名称到列值的映射片段，然后从那里进行解组来工作。

// 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
}

以上是将 AWS redshiftdataapiservice.GetStatementResultOutput 转换为 JSON 或结构的详细内容。更多信息请关注PHP中文网其他相关文章！