Nested attribute projection

[crocco95]

Hi all,
I have a single table with a deeply nested structure, I can represent it with a toy example:

{
"id": 123, #HashKey
"category": "example",
"houses": [
    {
      "id": 1
      "name": "Joe"
      "dogs": [
        {
          "id": 123
          "name": "Markus"
        },
        {
          "id": 890
          "name": "Markus"
        }
      ]
    },
    {
      "id": 2
      "name": "Hellen"
      "dogs": [
        {
          "id": 456
          "name": "Foofy"
        }
      ]
    },
  ]
}

I can easily get the main object using the hash-key "123" but then I would like to retrieve and return just the list of the dogs inside the house identified by the id "1".
Using the Model.get(1, {attributes: [ ... ]}) I can retrieve the object and using "attributes" I can specify simple attributes like "category" or the entire "houses" array, but I did not find a way to go deep inside the object.

Is there a way or it's impossible ? If the response will be the second case, which might be the right approach? Using Array.filter() on "houses" array on nodejs backend application?

If is there something unclear or wrong let me know and I will correct/explain asap.

Thanks.

[ptejada]

What you are asking is not possible today. In your backend, you can filter down the houses list using Array.filter() if you still want a list or Array.find() if you only want to get one item. It is also worth mentioning that the ProjectionExpression itself only allows you to specify a path and not a condition like getting the house with id=2 from the list. Unless your list is saved in the right order every time otherwise you will always need to fetch the whole list anyways and do the filtering in your backend.

This is why using paths in the current attributes option does not work. All attribute names included in ProjectionExpression are escaped through the ExpressionAttributeNames option. For example: Model.get(123, {attributes: ['houses[0]']}) would generate the following command

{
      Key: { pk: { N: 123 } },
      TableName: 'Model',
      ProjectionExpression: '#a0',
      ExpressionAttributeNames: { '#a0': 'houses[0]' }
}

When the generated command should be something like this

{
      Key: { pk: { N: 123 } },
      TableName: 'Model',
      ProjectionExpression: 'houses[0]'
}

What should be the next step here? How do we move this forward from the question to a feature request?

[crocco95]

Thanks for replying to me, I understand the situation. At the moment I think we will proceed with a backend filter because the size of the object is big but not enormous. We will monitor the costs and the performance as well to understand the impact of the solution in the long term. Thanks again for the detailed explanation.

[ptejada]

Even if you aren't able to extract exactly what you need with ProjectionExpression in this case, remember that if the use case does come up for it you can always generate the request and execute it manually.

You can use the dynamoose API to generate the request like this

const request = Model.get(1, {return: 'request'})

Aler the request object as needed

request.ProjectionExpression = 'house[0]'

Then finally manually execute the request

const result = await dynamoose.aws.ddb().getItem(request)

It is a lot more steps but it can be useful in those special cases where you do need this kind of optimization.

[fishcharlie]

Closing as a duplicate of #1589 to keep everything in one place.