Stringify JSON object for SQL query in AWS AppSync

Question

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

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
}

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\"",
    ]
}

This throws me error:

Invocation of method 'toString' in class com.amazonaws.deepdish.transform.util.TransformUtils threw exception java.lang.IllegalArgumentException: wrong number of arguments at ...

If I do:

{
    "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\"",
    ]
}

This throws me error:

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

If I do:

{
    "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: invalid input syntax for type json\n Detail: Token \"connectionType\" is invalid.\n Position: 222\n Where: JSON data, line 1: {connectionType..."}

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\"",
    ]
}

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.

Answer 1

You can build a JSON variable like this:

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

And insert in your query:

{
    "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\"",
    ]
}

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

Or directly:

{
    "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\"",
    ]
}