DynamoDB: query nested items from a map

Question

I have this data in our DynamoDB:

```
  "stateHistory": {
    "L": [
      {
        "M": {
          "state": {
            "S": "confirmed"
          },
          "timestamp": {
            "N": "1628579118359"
          }
        }
      },
      {
        "M": {
          "state": {
            "S": "active"
          },
          "timestamp": {
            "N": "1628579118988"
          }
        }
      },
      {
        "M": {
          "state": {
            "S": "finished"
          },
          "timestamp": {
            "N": "1628579251554"
          }
        }
      },
      {
        "M": {
          "state": {
            "S": "archived"
          },
          "timestamp": {
            "N": "1628583091657"
          }
        }
      }
```
And now we want to put all of those states into a txt output file. We tried it like this:

aws --profile odm-ss dynamodb scan --table-name booking.history_booking --query "Items[*].[bookingId.S,(stateHistory.L.0.M.state.S)] " --output text> data.txt

But get this error:

Bad value for --query Items[*].[(stateHistory.L.0.M.state.S)]: Expecting: ['quoted_identifier', 'unquoted_identifier', 'lbracket', 'lbrace'], got: number: Parse error at column 26, token "0" (NUMBER), for expression:
"Items[*].[(stateHistory.L.0.M.state.S)]"
                           ^

Any suggestions on how to fetch the states from this map using AWS CLI?

Answer

Hi, 
The Scan command you are looking for looks like this:

`aws dynamodb scan --table-name cust --query 'Items[*].stateHistory.L[*].M.state'`

```
aws dynamodb scan --table-name cust --query 'Items[*].stateHistory.L[*].M.state'
[
    [
        {
            "S": "confirmed"
        },
        {
            "S": "active"
        },
        {
            "S": "finished"
        },
        {
            "S": "archived"
        }
    ]
]
```

Or you can narrow it down further with:

`aws dynamodb scan --table-name cust --query 'Items[*].stateHistory.L[*].M.state.S' --output text`
```
aws dynamodb scan --table-name cust --query 'Items[*].stateHistory.L[*].M.state.S' --output text
confirmed       active  finished        archived
```

Answer

Thank you! Exactly what I needed! 👍
I modified it slightly, so that the output in the text is easy to process via Excel:

```
...,(stateHistory.L[0].M.state.S),(stateHistory.L[1].M.state.S),(stateHistory.L[2].M.state.S),(stateHistory.L[3].M.state.S),(stateHistory.L[4].M.state.S)]" --output text> data.txt
```
Follow-up question: How do I add headlines to each data field of the data.txt automatically so that I can use them as column names in Excel afterwards? 🤔

DynamoDB: query nested items from a map

관련 콘텐츠