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.

cyberwombat · Accepted Answer · 2020-07-20 22:09:25Z

2

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

edited Jul 20, 2020 at 22:09

answered Jul 18, 2020 at 21:18

cyberwombat

40.5k43 gold badges189 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Myz Over a year ago

You're the best. Your solution worked!! Since my JSON is not just fixed to connectionType and capacity keys so I modified your solution a bit. I'll post my solution just for reference if someone needs it. Thanks a lot!!

Myz · Accepted Answer · 2020-07-20 22:21:54Z

Solution 1:

Thanks to @cyberwombat I was able to fix this issue. I am posting this as a reference if someone needs it.

My serviceConfig object is not fixed and may change with time so I modified my resolver to make it more generic and it looks something like this:

#set($serviceConfigMap = {})

#foreach($key in $ctx.args.input.serviceConfig.keySet())
    $util.qr($serviceConfigMap.put("$key", $ctx.args.input.serviceConfig.get($key)))
#end

#set($serviceConfigMap = $util.toJson($serviceConfigMap))

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

Old Solution:

Here's how I solved this previously.

I converted my resolver to Pipeline and created a simple Lambda function in NodeJS to stringify the JSON object. This is how my lambda function looks like:

exports.handler = (event, context, callback) => {

    const response = {
        statusCode: 200,
        body: JSON.stringify(event.input.serviceConfig).replace(/\"/g, '\\\"'),
    };
    callback(null, response)
};

This is how serviceConfig looks like now and exactly how I wanted:

{\"connectionType\":\"ftth\",\"maxUploadCapacity\":1}"

I can now easily use it in my SQL statement within my resolver.

Collectives™ on Stack Overflow

Stringify JSON object for SQL query in AWS AppSync

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related