在 AWS AppSync 中为 SQL 查询字符串化 JSON 对象 [英] Stringify JSON object for SQL query in AWS AppSync

问题描述

问题:如何在 Appsync 速度模板中为 SQL 语句字符串化 JSON 对象?

Question: How can I stringify JSON object for SQL statement in my Appsync velocity template?

说明:我有一个 Aurora RDS 表，其中有一列数据类型为 JSON.AppSync API 已连接到 RDS.我的 GraphQL 架构看起来像

Explanation: I have an Aurora RDS table that has a column with data type JSON. AppSync API is already connected to RDS. My GraphQL schema looks like

input CreateServiceCatalogItemInput {
    serviceName: String!
    serviceConfig: ServiceConfigInput!
}

type Mutation {
    createServiceCatalogItem(input: CreateServiceCatalogItemInput!): ServiceCatalogItem
}

type Query {
    getAllServiceCatalogItem: [ServiceCatalogItem]
}

type ServiceCatalogItem {
    serviceId: ID!
    serviceName: String!
    serviceConfig: ServiceConfig!
}

type ServiceConfig {
    connectionType: String
    capacity: Int
}

input ServiceConfigInput {
    connectionType: String
    capacity: Int
}

schema {
    query: Query
    mutation: Mutation
}

我的 createServiceCatalogItem 突变解析器看起来像

My resolver for createServiceCatalogItem mutation looks like

{
    "version": "2018-05-29",
    "statements": [
        "INSERT INTO b2b_service_catalog(service_name, service_config) VALUES ('$ctx.args.input.serviceName', '$util.toString($ctx.args.input.serviceConfig)') RETURNING service_id AS "serviceId", service_name AS "serviceName", service_config AS "serviceConfig"",
    ]
}

这会引发我的错误:

在 com.amazonaws.deepdish.transform.util.TransformUtils 类中调用方法 'toString' 抛出异常 java.lang.IllegalArgumentException:...处的参数数量错误

如果我这样做:

{
    "version": "2018-05-29",
    "statements": [
        "INSERT INTO b2b_service_catalog(service_name, service_config) VALUES ('$ctx.args.input.serviceName', '$util.toJson($ctx.args.input.serviceConfig)') RETURNING service_id AS "serviceId", service_name AS "serviceName", service_config AS "serviceConfig"",
    ]
}

这会引发我的错误:

无法解析 JSON 文档:'意外字符('c'(代码 99)):期望用逗号分隔数组条目 at ...

Unable to parse the JSON document: 'Unexpected character ('c' (code 99)): was expecting comma to separate Array entries at ...

如果我这样做:

{
    "version": "2018-05-29",
    "statements": [
        "INSERT INTO b2b_service_catalog(service_name, service_config) VALUES ('$ctx.args.input.serviceName', '$ctx.args.input.serviceConfig') RETURNING service_id AS "serviceId", service_name AS "serviceName", service_config AS "serviceConfig"",
    ]
}

然后我得到错误，这是有道理的:

Then I get error which makes sense:

RDSHttp:{"message":"ERROR: json 类型的输入语法无效 详细信息:令牌"connectionType";无效. 位置:222 其中:JSON 数据，第 1 行:{connectionType..."}

RDSHttp:{"message":"ERROR: invalid input syntax for type json Detail: Token "connectionType" is invalid. Position: 222 Where: JSON data, line 1: {connectionType..."}

但是，当我在解析器中对 JSON 进行硬编码时，它可以工作:

However, when I hardcode the JSON in my resolver, it works:

{
    "version": "2018-05-29",
    "statements": [
        "INSERT INTO b2b_service_catalog(service_name, service_config) VALUES ('$ctx.args.input.serviceName', '{"connectionType":"ftth","capacity":1}') RETURNING service_id AS "serviceId", service_name AS "serviceName", service_config AS "serviceConfig"",
    ]
}

那么我如何将 {connectionType=ftth, capacity=1} 转换为 {connectionType":ftth", capacity":1}?我做错了什么或者我错过了什么?任何帮助将不胜感激.

So how can I convert {connectionType=ftth, capacity=1} to {"connectionType":"ftth", "capacity":1}? What am I doing wrong or am I missing something? Any help would be highly appreciated.

推荐答案

你可以像这样构建一个 JSON 变量:

You can build a JSON variable like this:

#set($json = $util.toJson({
  "connectionType": "$ctx.args.input.serviceConfig.connectionType",
  "capacity": $ctx.args.input.serviceConfig.capacity
  }))

并插入您的查询:

{
    "version": "2018-05-29",
    "statements": [
        "INSERT INTO b2b_service_catalog(service_name, service_config) VALUES ('$ctx.args.input.serviceName', '$util.escapeJavaScript($json)' RETURNING service_id AS "serviceId", service_name AS "serviceName", service_config AS "serviceConfig"",
    ]
}

由于所有的引号和转义，上述内容有点挑战性，但我认为使用 escapeJavaScript 可以解决问题.

The above is a bit challenging because of all the quotes and escaping but I think the use of escapeJavaScript will do the trick.

或直接:

{
    "version": "2018-05-29",
    "statements": [
        "INSERT INTO b2b_service_catalog(service_name, service_config) VALUES ('$ctx.args.input.serviceName', '{"connectionType":"$ctx.args.input.serviceConfig.connectionType","capacity": $ctx.args.input.serviceConfig.capacity}') RETURNING service_id AS "serviceId", service_name AS "serviceName", service_config AS "serviceConfig"",
    ]
}

这篇关于在 AWS AppSync 中为 SQL 查询字符串化 JSON 对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！