AppSync中处理PostgreSQL jsonb列的突变操作问题

我在项目中使用AppSync搭配Aurora Serverless PostgreSQL作为数据源，插入或更新包含jsonb列的表行时始终失败。

现有Request.VTL代码 #

#set($jsoncol = $util.toJson($ctx.args.coljsonb))

{
    "version": "2018-05-29",
    "statements": [
        "insert
into
table1 as t1 (col1,
col2,
col3,
coljsonb)
select
col1,
$ctx.args.col1 as col1,
$ctx.args.col2 as col2,
'$col3json'::jsonb as settings
from
supplier
where
uuid = '$ctx.args.input.id' on
conflict (col1) do
update
set
col1 = $ctx.args.col1,
col2 = $ctx.args.col2,
coljsonb = '$col3json'::jsonb
where
a.supplier = (
select
id
from
col1
where
uuid = '$ctx.args.input.id')"
         ]
}

现有GraphQL请求 #

{
  "query": "mutation ($id: ID!, $col2: Boolean!, $col3: Int!, $coljsonb: AWSJSON!) { upsertActiveRegistrySetting(id: $id, col2: $col2, col3: $col3, coljsonb: $coljsonb) }",
  "variables": {
    "id": "uuid",
    "col2": true,
    "col3": 94029,
    "settings": "{\"att1\": true}"
  }
}

具体问题 #

请求映射后，JSON值被额外引号包裹，变成 "{"att1": true}"，而预期的是不带外层引号的{"att1": true}。尝试过两种方法均失败：

• 解析后再序列化：$util.toJson($util.parseJson($ctx.args.coljsonb))，结果变成"{\"att1\"=true}"（使用等号而非冒号）
• 直接用原始值：$ctx.args.coljsonb，结果同样是"{\"att1\"=true}"

错误原因分析 #

1. 变量名不匹配：VTL里定义的变量是$jsoncol，但SQL语句中用的是$col3json，属于笔误，导致实际引用未定义变量
2. JSON字符串处理错误：将JSON值用单引号包裹后转jsonb，PostgreSQL会把带外层引号的字符串解析成包含JSON字符串的jsonb对象，而非预期的JSON对象
3. GraphQL变量参数不匹配：mutation定义的参数是coljsonb，但请求变量里传的是settings，导致AppSync无法正确接收参数值
4. SQL语法错误：select语句里重复声明col1，update子句里的表别名a未定义（应该用t1）
5. 直接拼接参数存在风险：直接把$ctx.args拼到SQL里，不仅容易出错，还存在SQL注入隐患

修正方案 #

1. 修正GraphQL请求

确保变量名与mutation参数一致：

{
  "query": "mutation ($id: ID!, $col2: Boolean!, $col3: Int!, $coljsonb: AWSJSON!) { upsertActiveRegistrySetting(id: $id, col2: $col2, col3: $col3, coljsonb: $coljsonb) }",
  "variables": {
    "id": "uuid",
    "col2": true,
    "col3": 94029,
    "coljsonb": "{\"att1\": true}"
  }
}

2. 修正Request.VTL

使用参数化查询避免拼接错误，同时正确处理jsonb列：

#set($parsedJson = $util.parseJson($ctx.args.coljsonb))
#set($formattedJson = $util.toJson($parsedJson))

{
    "version": "2018-05-29",
    "statements": [
        "INSERT INTO table1 AS t1 (col1, col2, col3, coljsonb)
         SELECT s.id, :col1, :col2, :jsoncol::jsonb
         FROM supplier s
         WHERE s.uuid = :id
         ON CONFLICT (col1) DO UPDATE
         SET col2 = :col2, col3 = :col3, coljsonb = :jsoncol::jsonb
         WHERE t1.col1 = (SELECT id FROM supplier WHERE uuid = :id)"
    ],
    "variableMap": {
        ":id": "$ctx.args.id",
        ":col1": "$ctx.args.col1",
        ":col2": "$ctx.args.col2",
        ":col3": "$ctx.args.col3",
        ":jsoncol": "$formattedJson"
    }
}

关键修正点说明

• 使用variableMap做参数化查询，避免直接拼接字符串导致的转义和SQL注入问题
• 先解析$ctx.args.coljsonb（AWSJSON类型传入的是字符串），再序列化为标准JSON字符串，确保格式正确
• 修正SQL中的表别名错误，去掉重复的col1声明
• 统一变量名，VTL中定义的$formattedJson与SQL中的:jsoncol对应

内容的提问来源于stack exchange，提问作者gokublack