AWS AppSync RDS: $util.rds.toJSONObject() Nested Objects

Question

I am using Amazon RDS with AppSync. I've created a resolver that join two tables to get One-to-One association between them and returns columns from both tables. What I would like to do is to be able to put nest some columns under a key in the resulting parsed JSON object evaluated using $util.rds.toJSONObject().

Here's the schema:

type Parent {
    col1: String
    col2: String
    child: Child
}

type Child {
    col3: String
    col4: String
}

Here's the resolver:

{
    "version": "2018-05-29",
    "statements": [
        "SELECT parent.*, child.col3 AS `child.col3`, child.col4 AS `child.col4` FROM parent LEFT JOIN child ON parent.col1 = child.col3"
    ]
}

I tried naming the resulting column with dot-syntax but, $util.rds.toJSONObject() doesn't put col3 and col4 under child key. The reason it should is because otherwise, Apollo won't be able to cache and parse the entity.

Note: Dot-syntax is not documented anywhere. Usually, some ORMs use dot-syntax technique to convert SQL rows to proper nested JSON objects.

Answer 1

The @Aaron_H comment and answer were useful for me, but the response mapping template provided in the answer didn't work for me. I managed to get a working response mapping template for my case which is similar for the case in question. In images below you will find info for query -> message(id: ID) { ... } (one message and the associated user will be returned):

1. SQL request to user table;
2. SQL request to message table;
3. SQL JOIN tables request for message id=1;
4. GraphQL Schema;
5. Request and response templates;
6. AWS AppSync query.

https://github.com/xai1983kbu/apollo-server/blob/pulumi_appsync_2/bff_pulumi/graphql/resolvers/Query.message.js

---

Next example for query messages

https://github.com/xai1983kbu/apollo-server/blob/pulumi_appsync_2/bff_pulumi/graphql/resolvers/Query.messages.js

Answer 2

Assuming your resolver is expecting to return a list of Parent types, ie. [Parent!]!, you can write your response mapping template logic like this:

#if($ctx.error)
    $util.error($ctx.error.message, $ctx.error.type)
#end

#set($output = $utils.rds.toJsonObject($ctx.result)[0])

## Make sure to handle instances where fields are null
## or don't exist according to your business logic

#foreach( $item in $output )
    #set($item.child = {
        "col3": $item.get("child.col3"),
        "col4": $item.get("child.col4")
    })
#end

$util.toJson($output)