Amplify-cli: How to Filter by Query on Nested Fields in AWS AppSync

[humanatwork]

Not sure if a bug or an implementation error on my part, but starting with the assumption this is just a usage mistake. Originally, posted on SO.

Which Category is your question related to?
api

What AWS Services are you utilizing?
AppSync, DynamoDB

Provide additional details e.g. code snippets

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 as well as the documentation for $utils.transform.toDynamoDBFilterExpression (here).

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": "[email protected]"
      },
      {
        "agentID": "[email protected]"
      }
    ],
    "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?

[RossWilliams]

I'm not aware of any trick you are missing to get this working, $util.transform.toDynamoDBFilterExpression does not support nested object filtering. Sounds like a great feature request.

You can test the appsync simulator $util.transform.toDynamoDBFilterExpression method to see the output it generates, assuming the simulator code faithfully replicates the Java code. The file is here. As you have noticed, nested filtering is not supported by this function.

[humanatwork]

Absolutely, that makes sense. Was worth the effort. Would definitely recommend this as a feature request. Anything I need to do get that going?

Here're the latest updates I've gathered:

Update 9/17/19

After some more fiddling, I happened upon the solution implied here. Following from what I could glean about solution, I successfully implemented a hardcoded nested query filter using the following VTL request resolver (and changing the filter expression keyname to avoid a reserved word conflict on data):

#set( $filter = $ctx.args.filter )
#set( $path = $filter.filterData ) ## currently, unused

{
    "version" : "2017-02-28",
    "operation" : "Query",
    "index" : "listings-index",
    "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" : {
        "expression" : "#filterData.beds = :beds",
        "expressionValues" : {
            ":beds" : $util.dynamodb.toDynamoDBJson(2.0)
        }
    },
    "limit": $util.defaultIfNull($ctx.args.limit, 20),
    "nextToken": $util.toJson($util.defaultIfNullOrEmpty($ctx.args.nextToken, null))
}

This returns my expected result:

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

Seems like progress, but any ideas on how to dynamically create the docpath and create an expression name for a nested attribute? Running more ideas and will report back if anything interesting shows up...

Second Update from 9/17/19

After further playing around with the request resolver, I think I've found a quick and dirty way to dynamically grab the path and target vars for creating a filter expression for my nested attributes. _Note_: The whole thing still returns an empty result set and it assumes there's only one filter key (for now), but the reserved keyword bit seems to have been solved. Still wondering why the results aren't showing up as expected though.

#set( $filter = $ctx.args.filter )
#foreach( $parent in $filter.keySet() )
    #set( $path = $parent )
#end
#set( $target = $filter[$path] )
#foreach( $ff in $target.keySet() ) ## should only contain one Map key-value pair
    #set( $fp = $ff )
#end
#set( $fv = $target[$fp] )

{
    "version" : "2017-02-28",
    "operation" : "Query",
    "index" : "listings-index",
    "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" : {
        "expression" : "#ffp = :$fp",  ## filter path parent.target = :target
        "expressionNames" : {
            "#ffp" : "${path}.${fp}"
        },
        "expressionValues" : {
            ":$fp" : $util.dynamodb.toDynamoDBJson(${fv.eq}), ## :target : value to filter for
        }
    },
    "limit": $util.defaultIfNull($ctx.args.limit, 200),
    "nextToken": $util.toJson($util.defaultIfNullOrEmpty($ctx.args.nextToken, null))
}

Inspecting the CloudWatch log transformedTemplate shows the expression names and values are being substituted appropriately:

"filter" : {
    "expression\" : "#ffp = :beds",
    "expressionNames" : {
        "#ffp" : "filterData.beds"
    },
    "expressionValues" : {
        ":beds" : { "N": 2.0 }
    }
}

The dynamic key creation could be expanded to accomplish any arbitrarily nested data structure, but I'm still not certain what the holdup is on the actual filter comparison.

[humanatwork]

Update from 09/18/19

I may have finally discovered the root of the problem: it seems that the way in which expressionNames are evaluated does not allow for a key to be a docpath. If I run either of the following filters (notice the use of a non-reserved DynamoDB keyword to illustrate the problem is with expression name substitution), I'll get the result I'm looking for:

"filter" : {
    "expression" : "filterData.beds = :beds",  ## filter path parent.target = :target
        "expressionValues" : {
            ":beds" : $util.dynamodb.toDynamoDBJson(${fv.eq}) ## :target : value to filter for
        }
    }

or

"filter" : {
    "expression" : "filterData.beds = :${fp}",  ## filter path parent.target = :target
        "expressionValues" : {
            ":{fp}" : $util.dynamodb.toDynamoDBJson(${fv.eq}) ## :target : value to filter for
        }
    }

Now, if I make a minor change, only attempting to substitute with an expression name value

"filter" : {
    "expression" : "#filterData.beds = :${fp}",  ## filter path parent.target = :target
        "expressionNames": {
            "#filterData.beds" : "filterData.beds"
        },
        "expressionValues" : {
            ":{fp}" : $util.dynamodb.toDynamoDBJson(${fv.eq}) ## :target : value to filter for
        }
    }

I get the following error message:

"ExpressionAttributeNames contains invalid key: Syntax error; key: \"#filterData.beds\" (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException"

Even with a hardcoded path substitution, VTL seems to read the path as a single key name. Same issue when swapping the values of the expressions dynamically so there're no hardcoded strings.

[humanatwork]

SOLVED

I happened upon this gem by accident, and it gave me the little bit extra I needed to find a workable solution with dynamic key names!

Here's what the filter expression looks like now:

"filter" : {
        "expression" : "#path.#filter = :${fp}",  ## filter path parent.target = :target
        "expressionNames": {
            "#path" : "${path}",
            "#filter" : "${fp}"
        },
        "expressionValues" : {
            ":${fp}" : $util.dynamodb.toDynamoDBJson(${fv.eq}) ## :target : value to filter for
        }
    }

The hold up here was that while expression attribute names are generally interpreted as document paths, with the introduction of the substituted name, the interpreter treats the key name as a scalar attribute and NOT as a document path. You need to individually identify the path elements and substitute for each.

Still would love to have a native solution if we can get it!

[zahydo]

Hi everybody, is there some update on this?

[wooklym]

+1

[bishonbopanna]

+1

[hozher]

Its been almost a year and we are still looking for this feature :/

[brandonking7]

This feature is really needed...Holding my team back.

[humanatwork]

Hi everybody, is there some update on this?

@zahydo no update as far as I know, unfortunately. The workaround I mentioned above requires manually designing the dynamic key structure through VTL loops and doesn't take advantage of the built-in transformer functionality (not ideal, at all).

As @RossWilliams mentioned, if you take a look here, you can see the structure of the generateFilterExpression() function that governs this behavior. Perhaps you or someone else (@hozher, @wooklym, @brandonking7, @attilah, @SwaySway, @yuth) might be interested in submitting a PR for this functionality? 🤔 I'd attempt it myself, but work has me fully occupied atm, and we ended up moving away from the use case that would've required this (partly for business reasons, but also due to this limitation). Anyone want to work on this further?

[hozher]

Hi,
Since I am using app-sync via CloudFormation I came up with this workaround as VTL template, as you can see it is a pretty dirty implementation, it does the job though:

######################################################################################################
## Since app-sync does not provide a mechanisim to search for nested objects.                       ##
## This is a workaround to provide this missing feature, please take into consideration             ##
## this implementation is very weak and does need to be updated when AWS Provides such mechanisim   ##
## For more information please follow https://github.com/aws-amplify/amplify-cli/issues/2311        ##
## As well as VTL reference: https://velocity.apache.org/engine/1.7/vtl-reference.html              ##
######################################################################################################

## Prepare all necessary for replacing built-in serach mechanisim.
#set( $filter = $ctx.args.filter )
#set( $path = "" )
#set( $value = "" )
#set( $cmd = "")
#set( $attributes = {})
## This loop supports up to teen levels of nesting.
#foreach($prop1 in $filter.keySet())
  ##set( $path = "$path" )
  #set( $value = $filter[$prop1] )
  #foreach($prop2 in $filter[$prop1].keySet() )
    #set( $cmd = $prop2)
    #set( $path = "#$prop1" )
    #set( $tmp = $attributes.put("#$prop1",$prop1))
    #set( $value = $filter[$prop1][$prop2] )
    #foreach($prop3 in $filter[$prop1][$prop2].keySet() )
      #set( $cmd = $prop3)
      #set( $path = "$path.#$prop2" )
      #set( $tmp = $attributes.put("#$prop2",$prop2))
      #set( $value = $filter[$prop1][$prop2][$prop3] )
      #foreach($prop4 in $filter[$prop1][$prop2][$prop3].keySet() )
        #set( $cmd = $prop4)
        #set( $path = "$path.#$prop3" )
        #set( $tmp = $attributes.put("#$prop3",$prop3))
        #set( $value = $filter[$prop1][$prop2][$prop3][$prop4] )
        #foreach($prop5 in $filter[$prop1][$prop2][$prop3][$prop4].keySet() )
          #set( $cmd = $prop5)
          #set( $path = "$path.#$prop4" )
          #set( $tmp = $attributes.put("#$prop4",$prop4))
          #set( $value = $filter[$prop1][$prop2][$prop3][$prop4][$prop5] )
          #foreach($prop6 in $filter[$prop1][$prop2][$prop3][$prop4][$prop5].keySet() )
            #set( $cmd = $prop6)
            #set( $path = "$path.#$prop5" )
              #set( $tmp = $attributes.put("#$prop5",$prop5))
              #set( $value = $filter[$prop1][$prop2][$prop3][$prop4][$prop5][$prop6] )
              #foreach($prop7 in $filter[$prop1][$prop2][$prop3][$prop4][$prop5][$prop6].keySet() )
                #set( $cmd = $prop7)
                #set( $path = "$path.#$prop6" )
                #set( $tmp = $attributes.put("#$prop6",$prop6))
                #set( $value = $filter[$prop1][$prop2][$prop3][$prop4][$prop5][$prop6][$prop7] )
                #foreach($prop8 in $filter[$prop1][$prop2][$prop3][$prop4][$prop5][$prop6][$prop7].keySet() )
                  #set( $cmd = $prop8)
                  #set( $path = "$path.#$prop7" )
                  #set( $tmp = $attributes.put("#$prop7",$prop7))
                  #set( $value = $filter[$prop1][$prop2][$prop3][$prop4][$prop5][$prop6][$prop7][$prop8] )
                  #foreach($prop9 in $filter[$prop1][$prop2][$prop3][$prop4][$prop5][$prop6][$prop7][$prop8].keySet() )
                    #set( $cmd = $prop9)
                    #set( $path = "$path.#$prop8" )
                    #set( $tmp = $attributes.put("#$prop8",$prop8))
                    #set( $value = $filter[$prop1][$prop2][$prop3][$prop4][$prop5][$prop6][$prop7][$prop8][$prop9] )
                    #foreach($prop10 in $filter[$prop1][$prop2][$prop3][$prop4][$prop5][$prop6][$prop7][$prop8][$prop9].keySet() )
                      #set( $cmd = $prop10)
                      #set( $path = "$path.#$prop9" )
                      #set( $tmp = $attributes.put("#$prop9",$prop9))
                      #set( $value = $filter[$prop1][$prop2][$prop3][$prop4][$prop5][$prop6][$prop7][$prop8][$prop9][$prop10] )
                    #end
                  #end
                #end
              #end
            #end
          #end
        #end  
      #end  
    #end  
#end

## It prepares the filter condition based on command
#if( $cmd == "eq" )
  #set ( $expression = "($path = :value)")
#elseif( $cmd == "ne" )
  #set ( $expression = "($path <> :value)")
#elseif( $cmd == "contains" )
  #set ( $expression = "(contains($path, :value))")
#elseif( $cmd == "notContains")
  #set ( $expression = "(NOT contains($path, :value))")
#elseif( $cmd == "beginsWith")
  #set ( $expression = "(begins_with($path, :value))")
#else
  #set ( $expression = "($path = :value)")
#end

## It sets the filter expression 
#set ( $custom = {
  "expression": $expression,
  "expressionNames": $attributes,
  "expressionValues": { ":value": { "S": $value } }
})

## The actual template
{
  "version": "2017-02-28",
  "operation": "Scan",
  "filter": #if($context.args.filter) $util.toJson($custom) #else null #end,
  "limit": #if($context.args.limit) $ctx.args.limit #else null #end,
  "nextToken": $util.toJson($util.defaultIfNullOrBlank($ctx.args.nextToken, null))
}

[asmiki]

I am killing myself for the last 4 days trying to figure this out, and any help will be greatly appreciated. I was trying @humanatwork step by step both hardcoded and parametrized approach and never was able to get filtered data back. Also tried @hozher way with path builder but that also didn't solve it. Now wondering if it is even possible :(

Here is my model. For the simplicity sake, I used family tree types and properties

type Person
  @model
  {
  id: ID!
  title: String
  gender: String
  generation: Int
  descendants: [PersonRelationship] @connection(keyName: "PersonsByAncestor", fields: ["id"])
  ancestors: [PersonRelationship] @connection(keyName: "PersonsByDescendant", fields: ["id"])
}


type PersonRelationship
  @model
  @key(
    name: "PersonsByAncestor"
    fields: [ "ancestorID" ]
  )
  @key(
    name: "PersonsByDescendant"
    fields: [ "descendantID" ]
  ) {
  id: ID!
  ancestorID: ID!
  descendantID: ID!
  ancestor: Person! @connection(fields: ["ancestorID"])
  descendant: Person! @connection(fields: ["descendantID"])
}

type PersonRelationshipConnection {
  items: [PersonRelationship]
  nextToken: String
}

type Query {
  listDescendants(ancestorID: ID!, generation: Int, gender: String, limit: Int, nextToken: String): PersonRelationshipConnection
}

I would like to query PersonRelationship with specific ancestorID, but provide a filter which would filter data in Person type. Something like:

• get all direct children by providing ancestorID and generation number
• get all female descendants by providing ancestorID and gender string

This is my request VTL. Without filter it returns all descendants for ancestorID. But when I want to filter by descendant's field, it always return empty list of items

#set( $limit = $util.defaultIfNull($context.args.limit, 10) )
{
  "version": "2017-02-28",
  "operation": "Query",
  "query": {
    "expression": "#connectionAttribute = :connectionAttribute",
    "expressionNames": {
        "#connectionAttribute": "ancestorID"
    },
    "expressionValues": {
        ":connectionAttribute": {
            "S": "$context.args.ancestorID"
        }
    }
  },
 "filter" : {
      "expression": "#d.#g = :dg",
        "expressionNames": {
            "#d": "descendant",
            "#g": "generation"
        },
        "expressionValues": {
            ":dg": {
                "N": 1
            }
        }
  },
  "scanIndexForward": true,
  "limit": $limit,
  "nextToken": #if( $context.args.nextToken ) "$context.args.nextToken" #else null #end,
  "index": "PersonsByAncestor"
}

Thanks in advance for any help.
cc: @mikeparisstuff @yuth

[RossWilliams]

Templates are used to get and filter data from a single table. You are asking to filter results in one table based on information in a second table. This is not going to work.

One option is to include the filtering information in your relationship table. You can add a “descendentGender” and “descendantGeneration” to the relationship model.

[asmiki]

Thanks, @RossWilliams. My thinking was that if these nested items can be retrieved with all the properties populated, there must be a way to filter by these fields, without going the route of duplicating filter properties.

[RossWilliams]

My thinking was that if these nested items can be retrieved with all the properties populated

Don't think of these as nested items in a table. These are many different items combined within AppSync _after_ multiple independent calls to the database.

[asmiki]

I was inspecting the execution in CloudWatch and understand that there are multiple calls to the database. But you said above:

Templates are used to get and filter data from a single table. You are asking to filter results in one table based on information in a second table. This is not going to work.

And there was my hold up. If there are multiple calls to the different DynamoDB tables, could these calls be altered so it brings back a filtered list of items from the "nested" table instead of returning all items?

[mormsbee]

@asmiki This is my question right here. I'm trying to solve this very issue right now. Have you had any luck since Sep?

[asmiki]

I went with @RossWilliams suggestion to duplicate properties that I need for filtering or searching...it is a little ugly IMO, but it works ok.