amazon-web-servicesaws-appsyncvtl

Issue with handling jsonb columns in appsync mutations


I use appsync with aurora serverless postgres as the datasource in my project, I need to insert or update a row in a table which contain a jsonb column, but always having an issue in inserting it.

My 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')"
         ]
    }

My request to graphql api

   {
  "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}"
  }
}

The exact problem I have is, after the request mapping the transformedTemplate as the json value like the stringified version with "", I am not quite sure why it is adding it, I mean it was represented like "{\"att1\": true}" but I want it instead to look like this "{"att1": true}"...

I tried a few options, like

  1. parse the input ($util.toJson($util.parseJson($ctx.args.coljsonb))) and stringify it back -> but it also resulted bad, when I just parsed it becomes like this "{"att1"=true}" with an equal sign.

  2. I tried just using the exact value of $ctx.args.coljsonb, it still shows like this "{"att1"=true}"

what is the mistake I am doing here?


Solution

  • I got it working using $utils.escapeJavaScript

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