How to write a AWS AppSync response mapping template for an RDS data source

I have been following this guide for querying an Aurora Serverless database through an AppSync schema. Now I want to run a couple of queries at the same time with a request mapping like:

{
    "version": "2018-05-29",
        "statements": [
            "SELECT * FROM MyTable WHERE category='$ctx.args.category'",
            "SELECT COUNT(*) FROM MyTable WHERE category='$ctx.args.category'",
    ]
}

So, how to handle multiple selects in the response mapping? The page has a few examples, but none has two selects:

$utils.toJson($utils.rds.toJsonObject($ctx.result)[0])    ## For first item results
$utils.toJson($utils.rds.toJsonObject($ctx.result)[0][0]) ## For first item of first query
$utils.toJson($utils.rds.toJsonObject($ctx.result)[1][0]) ## For first item of second query
$utils.toJson($utils.rds.toJsonObject($ctx.result)??????) ## ?? For first & second item results

I predicted the response type to be like follows, but is not strict as long as I can get the values.

type MyResponse {
    MyResponseItemList [MyResponseItem]
    Count Int
}

type MyResponseItem {
  Id: ID!
  Name: String
  ...
}

Doing two selects will not work with AppSync.

I suggest you either break apart the two SQL queries into two different GraphQL query operations or combine the two SQL queries into one.

I faced the same issue and got this working as follows.

Instead of having Count as a direct Int type result, I converted that into a another type called PaginationResult.

type MyResponse {
    MyResponseItemList [MyResponseItem]
    Count PaginationResult
}

type PaginationResult {
   Count Int
}

type MyResponseItem {
...
}

Response Velocity Template

#set($resMap = {
    "MyResponseItemList": $utils.rds.toJsonObject($ctx.result)[0],
    "Count": $utils.rds.toJsonObject($ctx.result)[1][0]
})
$util.toJson($resMap)

FWIW, I just got working a UNION ALL Appsync/RDS Request resolver query with two SELECTs:

  {  
    "version": "2018-05-29",   
    "statements": ["SELECT patientIDa, patientIDb, distance FROM Distances WHERE patientIDa='$ctx.args.patientID' UNION ALL SELECT patientIDb, patientIDa, distance FROM Distances WHERE patientIDb='$ctx.args.patientID'"]  
}

Not sure if this will help the OP but it may.

***Note: in my case (maybe because I'm on windows) the ENTIRE ["SELECT...] statement needs to be on one line (no cr/lf) or else graphql errors with "non-escaped character..." (testing using GraphiQL)