How to Filter by Query on Nested Fields in AWS AppSync

Problem and Expected Results

I'm using a proof of concept schema and DynamoDB Table setup to filter on nested field values. I've followed the ideas very generally here (https://medium.com/open-graphql/implementing-search-in-graphql-11d5f71f179) as well as the documentation for $utils.transform.toDynamoDBFilterExpression (https://docs.aws.amazon.com/appsync/latest/devguide/resolver-util-reference.html#transformation-helpers-in-utils-transform).

The basic idea is this: using the same sort of principles, I'd like to filter by any arbitrarily deep nested field (short of the 32 document path length limit in DynamoDB). The relevant setup looks like this:

AppSync schema (apologies for the naming conventions; was supposed to be a quick and dirty PoC):

query {
    listActiveListingsBySubAndFilter(
        filter: TableTestMasterDataTable_ImportV1FilterInput!,
        limit: Int,
        nextToken: String
    ): TestMasterDataTable_ImportV1Connection
}

input TableBooleanFilterInput {
	ne: Boolean
	eq: Boolean
}

input TableDataObjectFilterInput {
	beds: TableFloatFilterInput
	baths: TableFloatFilterInput
}

input TableFloatFilterInput {
	ne: Float
	eq: Float
	le: Float
	lt: Float
	ge: Float
	gt: Float
	contains: Float
	notContains: Float
	between: [Float]
}

input TableIDFilterInput {
	ne: ID
	eq: ID
	le: ID
	lt: ID
	ge: ID
	gt: ID
	contains: ID
	notContains: ID
	between: [ID]
	beginsWith: ID
}

input TableIntFilterInput {
	ne: Int
	eq: Int
	le: Int
	lt: Int
	ge: Int
	gt: Int
	contains: Int
	notContains: Int
	between: [Int]
}

input TableStringFilterInput {
	ne: String
	eq: String
	le: String
	lt: String
	ge: String
	gt: String
	contains: String
	notContains: String
	between: [String]
	beginsWith: String
}

input TableTestMasterDataTable_ImportV1FilterInput {
	id: TableStringFilterInput
	status: TableStringFilterInput
	sub: TableStringFilterInput
	data: TableDataObjectFilterInput
}

type TestMasterDataTable_ImportV1 {
	id: String!
	status: String!
	sub: String!
	data: AWSJSON
}

type TestMasterDataTable_ImportV1Connection {
	items: [TestMasterDataTable_ImportV1]
	nextToken: String
}

input UpdateTestMasterDataTable_ImportV1Input {
	id: String!
	status: String
	sub: String!
	data: AWSJSON
}

VTL request and response resolvers:

## Request resolver

#set( $filter = $ctx.args.filter )
#set( $path = $filter.data )

{
    "version" : "2017-02-28",
    "operation" : "Query",
    "index" : "listings-index",  ## GSI on table with HASH: status, RANGE: sub
    "query" : {
        "expression": "#status = :status and #sub = :sub",
        "expressionNames" : {
        	"#status" : "status",
            "#sub" : "sub"
    	},
        "expressionValues" : {
            ":status" : $util.dynamodb.toDynamoDBJson("Active"),
            ":sub" : $util.dynamodb.toDynamoDBJson($filter.sub.eq)
        }
    },
    "filter" : $util.transform.toDynamoDBFilterExpression($path),
    "limit": $util.defaultIfNull($ctx.args.limit, 20),
    "nextToken": $util.toJson($util.defaultIfNullOrEmpty($ctx.args.nextToken, null))
}


## Response resolver

{
    "items": $util.toJson($ctx.result.items),
    "nextToken": $util.toJson($util.defaultIfNullOrBlank($context.result.nextToken, null))
}

Example DynamoDB Table element:

{
  "_meta": {
    "exposure": 0.08,
    "lastActive": 1557800000,
    "lastUpdated": 1557878400,
    "lastView": 1557878500,
    "numViews": 63,
    "posted": 1557878400
  },
  "buildingID": "325-5th-Ave,-New-York,-NY-10016,-USA",
  "data": {
    "agent": [
      {
        "agentID": "daeo@gmail.com"
      },
      {
        "agentID": "ben@gmail.com"
      }
    ],
    "amenities": [
      "hot tub",
      "time machine"
    ],
    "baths": 2,
    "beds": 2
  },
  "id": "325-5th-Ave,-New-York,-NY-10016,-USA#37C:1557878400",
  "status": "Active",
  "sub": "new-york/manhattan/listings",
  "unitNum": "37C",
  "unitRefID": "325-5th-Ave,-New-York,-NY-10016,-USA#37C"
}

Based on all of this, if I run the following query:

listActiveListingsBySubAndFilter(filter: {
    "sub" : {
      "eq" : "new-york/manhattan/listings"
    },
    "data": {
      "beds": {
      	"eq": 2.0
      }
    }) {
    items {
      id
      status
    }
    nextToken
}

I would expect to get something like this in return:

{
  "data": {
    "listActiveListingsBySubAndFilter": {
      "items": [
          {
              "id": "325-5th-Ave,-New-York,-NY-10016,-USA#37C:1557878400",
              "status": "Active"
          }
      ],
      "nextToken": null
    }
  }
}

Note: this is the only expected return since there's only one item matching these requirements in the database at this time.

Actual Results

All of that said, the results I'm getting (or lack thereof) aren't making much sense. No matter the query (data.beds, data.baths), if the field is nested in data the return is the same:

{
  "data": {
    "listActiveListingsBySubAndFilter": {
      "items": [],
      "nextToken": null
    }
  }
}

I've verified the query is working as expected and the filter expression is formatted appropriately (it works on other non-nested fields like id). What's perplexing is that the filter just doesn't seem to get applied (or maybe is being applied in some non-intuitive way?). For reference, here's a snippet of a typical CloudWatch log for the above:

{
    "context": {
        "arguments": {
            "filter": {
                "sub": {
                    "eq": "new-york/manhattan/listings"
                },
                "data": {
                    "beds": {
                        "eq": 2
                    }
                }
            },
            "limit": 200
        },
        "stash": {},
        "outErrors": []
    },
    "fieldInError": false,
    "errors": [],
    "parentType": "Query",
    "graphQLAPIId": "q7ueubhsorehbjpr5e6ymj7uua",
    "transformedTemplate": "\n\n{\n    \"version\" : \"2017-02-28\",\n    \"operation\" : \"Query\",\n    \"index\" : \"listings-index\",\n    \"query\" : {\n        \"expression\": \"#status = :status and #sub = :sub\",\n        \"expressionNames\" : {\n        \t\"#status\" : \"status\",\n            \"#sub\" : \"sub\"\n    \t},\n        \"expressionValues\" : {\n            \":status\" : {\"S\":\"Active\"},\n            \":sub\" : {\"S\":\"new-york/manhattan/listings\"}\n        }\n    },\n    \"filter\" : {\"expression\":\"(#beds = :beds_eq)\",\"expressionNames\":{\"#beds\":\"beds\"},\"expressionValues\":{\":beds_eq\":{\"N\":2.0}}},\n    \"limit\": 200,\n    \"nextToken\": null\n}"
}

Notice the filter expressionValues value in transformedTemplate: { "N" : 2.0 } (sans $util.toDynamoDBJson formatting) and compare it to the value in the object in DynamoDB on that field.

I've tried everything, including changing the fields themselves to strings and doing various filter operations like eq and contains to see if this was some odd type inconsistency, but no luck.

As of now, I have two backup solutions that involve either "pulling up" all the relevant fields I might want to filter on (cluttering my records with attributes I'd rather keep nested) or creating a new nested type containing only high-level fields for filtering on -- i.e., effectively split the records into a record reference and a record filter reference. In this scenario, we'd get some "Listing" record that has as its data field value something like ListingFilterData -- e.g.:

type Listing {
    id: String!
    sub: String!
    status: String!
    data: ListingFilterData!
}

type ListingFilterData {
    beds: Float!
    baths: Float!
}

Both are doable, but I'd rather try to solve the current issue instead of adding a bunch of extra data to my table.

Any thoughts?

Edited by: april-labs on Sep 16, 2019 4:31 PM