appsync rds resolver - insert polygon

Question

I'm trying to insert an object containing a polygon using aws appsync resolvers into an aws aurora rds postgres instance which has POSTGis extension installed.

I have managed to create insert the new row when running the sql statement via the psql command line when ssh'ed into the rds instance, but the resolver is failing.

I have the following mutation resolver:

#set( $region = $ctx.args.region )
#set ( $created_at = $util.time.nowFormatted("yyyy-MM-dd HH:mm:ssZ"))
#set ( $updated_at = $util.time.nowFormatted("yyyy-MM-dd HH:mm:ssZ"))

{
    "version": "2018-05-29",
    "statements": [
        "INSERT INTO regions (name, created_at, updated_at, geo_json) VALUES ('$region.name', '$created_at', '$updated_at', ST_GeomFromGeoJSON('{"type": $region.geo_json.type, "coordinates": $region.geo_json.coordinates}'))",
        "SELECT id, created_at, updated_at, name, ST_AsGeoJSON(geo_json) FROM regions WHERE id=(SELECT LAST_INSERT_ID())"
    ]
}

When checking the logs, the statement seems to be created coorectly, yet I'm getting an error:

{
  "data": {
    "addRegion": null
  },
  "errors": [
    {
      "path": [
        "addRegion"
      ],
      "data": null,
      "errorType": "MappingTemplate",
      "errorInfo": null,
      "locations": [
        {
          "line": 2,
          "column": 3,
          "sourceName": null
        }
      ],
      "message": "Unexpected character ('t' (code 116)): was expecting comma to separate Array entries\n at [Source: (String)\"\n{\n    \"version\": \"2018-05-29\",\n    \"statements\": [\n        \"INSERT INTO regions (name, created_at, updated_at, geo_json) VALUES ('bigPoly', '2021-10-13 12:55:20+0000', '2021-10-13 12:55:20+0000', ST_GeomFromGeoJSON('{\"type\": Polygon, \"coordinates\": [[[52.737625837326036, 12.89819510842527], [45.735300779342644, 12.997057127577222], [48.9607000350952, 18.8981104603958], [52.737625837326036, 12.89819510842527]]]}'))\",\n        \"SELECT id, created_at, updated_at, name, ST_AsGeoJSON(geo_json) FROM r\"[truncated 51 chars]; line: 5, column: 169]"
    }
  ]
}

{
    "logType": "RequestMapping",
    "path": [
        "addRegion"
    ],
    "fieldName": "addRegion",
    "requestId": "637fccd7-6728-4b66-9b5d-cd9619677cc6",
    "context": {
        "arguments": {
            "region": {
                "name": "bigPoly",
                "geo_json": {
                    "type": "Polygon",
                    "coordinates": [
                        [
                            [
                                52.737625837326036,
                                12.89819510842527
                            ],
                            [
                                45.735300779342644,
                                12.997057127577222
                            ],
                            [
                                48.9607000350952,
                                18.8981104603958
                            ],
                            [
                                52.737625837326036,
                                12.89819510842527
                            ]
                        ]
                    ]
                }
            }
        },
        "stash": {},
        "outErrors": []
    },
    "fieldInError": true,
    "errors": [
        "Unable to transform the request mapping template."
    ],
    "parentType": "Mutation",
    "transformedTemplate": "\n{\n    \"version\": \"2018-05-29\",\n    \"statements\": [\n        \"INSERT INTO regions (name, created_at, updated_at, geo_json) VALUES ('bigPoly', '2021-10-13 12:55:20+0000', '2021-10-13 12:55:20+0000', ST_GeomFromGeoJSON('{\"type\": Polygon, \"coordinates\": [[[52.737625837326036, 12.89819510842527], [45.735300779342644, 12.997057127577222], [48.9607000350952, 18.8981104603958], [52.737625837326036, 12.89819510842527]]]}'))\",\n        \"SELECT id, created_at, updated_at, name, ST_AsGeoJSON(geo_json) FROM regions WHERE id=(SELECT LAST_INSERT_ID())\"\n    ]\n}\n"
}

Has anyone attempted to insert geo_json object into an rds instance using appsync resolvers? Any idea why this wouldn't work and what's the correct way of passing in the geojson object to the statement so that appsync accepts it?

Answer 1

Your template is generating this;

{    "version": "2018-05-29",    "statements": [        "INSERT INTO regions (name, created_at, updated_at, geo_json) VALUES ('bigPoly', '2021-10-13 12:55:20+0000', '2021-10-13 12:55:20+0000', ST_GeomFromGeoJSON('{"type": Polygon, "coordinates": [[[52.737625837326036, 12.89819510842527], [45.735300779342644, 12.997057127577222], [48.9607000350952, 18.8981104603958], [52.737625837326036, 12.89819510842527]]]}'))",        "SELECT id, created_at, updated_at, name, ST_AsGeoJSON(geo_json) FROM regions WHERE id=(SELECT LAST_INSERT_ID())"    ]}"
}

The issue here is that you have an unescaped double quote around "type"

....ST_GeomFromGeoJSON('{"type": Polygon, ....