By using AWS re:Post, you agree to the Terms of Use
/DynamoDB: query nested items from a map/

DynamoDB: query nested items from a map

0

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?

2 Answers
1

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
answered a month ago
0

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? 🤔

answered a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions