title: How to simplify aws DynamoDB query JSON output from the command line?
body: <p>I'm working with <a href="http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Tools.CLI.html" rel="noreferrer">The AWS Command Line Interface for DynamoDB</a>.</p>
  
  <p>When we query an item, we get a very detailed JSON output. You get something like this (it has been built from the <code>get-item</code> in order to be almost exhaustive (the <code>NULL</code> type has been omitted) <a href="http://docs.aws.amazon.com/cli/latest/reference/dynamodb/get-item.html" rel="noreferrer">aws command line help</a>:</p>
  
  <pre><code>{
      "Count": 1, 
      "Items": [
          {
              "Id": {
                  "S": "app1"
              }, 
              "Parameters": {
                  "M": {
                      "nfs": {
                          "M": {
                              "IP" : {
                                  "S" : "172.16.0.178"
                              }, 
                              "defaultPath": {
                                  "S": "/mnt/ebs/"
                              },
                              "key": {
                                  "B": "dGhpcyB0ZXh0IGlzIGJhc2U2NC1lbmNvZGVk"
                              },
                              "activated": {
                                  "BOOL": true 
                              }
                          }
                      },
                      "ws" : {
                          "M" : {
                              "number" : {
                                  "N" : "5"
                              },
                              "values" : {
                                  "L" : [
                                      { "S" : "12253456346346"},
                                      { "S" : "23452353463464"},
                                      { "S" : "23523453461232"},
                                      { "S" : "34645745675675"},
                                      { "S" : "46456745757575"}
                                  ]
                              }
                          } 
                      }
                  }
              },
              "Oldtypes": {
                  "typeSS" : {"SS" : ["foo", "bar", "baz"]},
                  "typeNS" : {"NS" : ["0", "1", "2", "3", "4", "5"]},
                  "typeBS" : {"BS" : ["VGVybWluYXRvcgo=", "VGVybWluYXRvciAyOiBKdWRnbWVudCBEYXkK", "VGVybWluYXRvciAzOiBSaXNlIG9mIHRoZSBNYWNoaW5lcwo=", "VGVybWluYXRvciA0OiBTYWx2YXRpb24K","VGVybWluYXRvciA1OiBHZW5lc2lzCg=="]}
              }
          }
      ], 
      "ScannedCount": 1, 
      "ConsumedCapacity": null
  }
  </code></pre>
  
  <p>Is there any way to get a simpler output for the <code>Items</code> part? Like this:</p>
  
  <pre><code>{
      "ConsumedCapacity": null,
      "Count": 1,
      "Items": [
          {
              "Id": "app1",
              "Parameters": {
                  "nfs": {
                      "IP": "172.16.0.178",
                      "activated": true,
                      "defaultPath": "/mnt/ebs/",
                      "key": "dGhpcyB0ZXh0IGlzIGJhc2U2NC1lbmNvZGVk"
                  },
                  "ws": {
                      "number": 5,
                      "values": ["12253456346346","23452353463464","23523453461232","34645745675675","46456745757575"]
                  }
              },
              "Oldtypes": {
                  "typeBS": ["VGVybWluYXRvcgo=", "VGVybWluYXRvciAyOiBKdWRnbWVudCBEYXkK", "VGVybWluYXRvciAzOiBSaXNlIG9mIHRoZSBNYWNoaW5lcwo=", "VGVybWluYXRvciA0OiBTYWx2YXRpb24K", "VGVybWluYXRvciA1OiBHZW5lc2lzCg=="],
                  "typeNS": [0, 1, 2, 3, 4, 5],
                  "typeSS": ["foo","bar","baz"]
              }
          }
      ],
      "ScannedCount": 1
  }
  </code></pre>
  
  <p>There is nothing helpful in the <a href="http://docs.aws.amazon.com/cli/latest/reference/dynamodb/index.html" rel="noreferrer">dynamodb - AWS CLI 1.7.10 documentation</a>.</p>
  
  <p>We must get the result from the command line. I'm willing to use other command line tools like <a href="http://stedolan.github.io/jq/" rel="noreferrer"><code>jq</code></a> if necessary, but such a <code>jq</code> mapping appears to complicated to me. </p>
  
  <p><br/>
  <strong>Update 1: <code>jq</code> based solution</strong> (with help from DanielH's answer)</p>
  
  <p>With <code>jq</code> it is easy, but not quite pretty, you can do something like: </p>
  
  <pre><code>$&gt; aws dynamodb query --table-name ConfigCatalog --key-conditions '{ "Id" : {"AttributeValueList": [{"S":"app1"}], "ComparisonOperator": "EQ"}}' | jq -r '.Items[0].Parameters.M."nfs#IP".S'
  </code></pre>
  
  <p>Result will be: <code>172.16.0.178</code></p>
  
  <p>The <code>jq</code> <code>-r</code> option gives you a raw output.</p>
  
  <p><br/>
  <strong>Update 2: <code>jq</code> based solution</strong> (with help from @jeff-mercado)</p>
  
  <p>Here is an updated and commented version of Jeff Mercado <code>jq</code> function to unmarshall DynamoDB output. It will give you the expected output:</p>
  
  <pre><code>$&gt; cat unmarshal_dynamodb.jq
  def unmarshal_dynamodb:
    # DynamoDB string type
    (objects | .S)
  
    # DynamoDB blob type
    // (objects | .B)
  
    # DynamoDB number type
    // (objects | .N | strings | tonumber)
  
    # DynamoDB boolean type
    // (objects | .BOOL)
  
    # DynamoDB map type, recursion on each item
    // (objects | .M | objects | with_entries(.value |= unmarshal_dynamodb))
  
    # DynamoDB list type, recursion on each item
    // (objects | .L | arrays | map(unmarshal_dynamodb))
  
    # DynamoDB typed list type SS, string set
    // (objects | .SS | arrays | map(unmarshal_dynamodb))
  
    # DynamoDB typed list type NS, number set
    // (objects | .NS | arrays | map(tonumber))
  
    # DynamoDB typed list type BS, blob set
    // (objects | .BS | arrays | map(unmarshal_dynamodb))
  
    # managing others DynamoDB output entries: "Count", "Items", "ScannedCount" and "ConsumedCapcity"
    // (objects | with_entries(.value |= unmarshal_dynamodb))
    // (arrays | map(unmarshal_dynamodb))
  
    # leaves values
    // .
    ;
  unmarshal_dynamodb
  </code></pre>
  
  <p>If you save the <code>DynamoDB</code> query output to a file, lets say <code>ddb-query-result.json</code>, you can execute to get desired result:</p>
  
  <pre><code>$&gt; jq -f unmarshal_dynamodb.jq ddb-query-result.json
  </code></pre>