How to simplify aws DynamoDB request JSON output from the command line?

Question

I am working with AWS Command Line Interface for DynamoDB .

When we request an element, we get a very verbose JSON output. You get something like this (it was built from get-item to be almost exhaustive ( NULL was omitted) aws command line help :

 { "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 } : [ "VGVybWluYXRvcgo =", "VGVybWluYXRvciAyOiBKdWRnbWVudCBEYXkK", "VGVybWluYXRvciAzOiBSaXNlIG9mIHRoZSBNYWNoaW5lcwo =", "VGVybWluYXRvciA0OiBTYWx2YXRpb24K", "VGVybWluYXRvciA1OiBHZW5lc2lzCg =="]} { "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 } 

Is there a way to get a simpler output for the Items part? Like this:

 { "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 } ", "VGVybWluYXRvciAyOiBKdWRnbWVudCBEYXkK", "VGVybWluYXRvciAzOiBSaXNlIG9mIHRoZSBNYWNoaW5lcwo =", "VGVybWluYXRvciA0OiBTYWx2YXRpb24K", "VGVybWluYXRvciA1OiBHZW5lc2lzCg =="], { "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 } 

There is nothing useful in dynamodb documentation - AWS CLI 1.7.10 .

We should get the result from the command line. I would like to use other command line tools like jq if necessary, but this jq seems complicated to me.

Update 1: jq solution (using DanielH answer)

With jq this is easy, but not quite pretty, you can do something like:

 $> aws dynamodb query --table-name ConfigCatalog --key-conditions '{ "Id" : {"AttributeValueList": [{"S":"app1"}], "ComparisonOperator": "EQ"}}' | jq -r '.Items[0].Parameters.M."nfs#IP".S' 

The result will be: 172.16.0.178

The jq -r jq provides you with the original output.

Update 2: jq solution (using @ jeff-mercado)

Here is an updated and commented version of the Jeff Mercado jq function to display DynamoDB results. It will give you the expected result:

 $> 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 

If you save the DynamoDB query output to a file, say ddb-query-result.json , you can execute it to get the desired result:

 $> jq -f unmarshal_dynamodb.jq ddb-query-result.json 

Answer 1

You can decode values ​​recursively with a well-designed function. Key names seem to match the type:

 S -> string N -> number M -> map 

Refer to each of the cases that you want to decode, if possible, otherwise filter it. You can use different types of filters and an alternative operator for this.

 $ cat input.json { "Count": 1, "Items": [ { "Id": { "S": "app1" }, "Parameters": { "M": { "nfs#IP": { "S": "192.17.0.13" }, "maxCount": { "N": "1" }, "nfs#defaultPath": { "S": "/mnt/ebs/" } } } } ], "ScannedCount": 1, "ConsumedCapacity": null } 

 $ cat ~/.jq def decode_ddb: def _sprop($key): select(keys == [$key])[$key]; # single property objects only ((objects | { value: _sprop("S") }) # string (from string) // (objects | { value: _sprop("B") }) # blob (from string) // (objects | { value: _sprop("N") | tonumber }) # number (from string) // (objects | { value: _sprop("BOOL") }) # boolean (from boolean) // (objects | { value: _sprop("M") | map_values(decode_ddb) }) # map (from object) // (objects | { value: _sprop("L") | map(decode_ddb) }) # list (from encoded array) // (objects | { value: _sprop("SS") }) # string set (from string array) // (objects | { value: _sprop("NS") | map(tonumber) }) # number set (from string array) // (objects | { value: _sprop("BS") }) # blob set (from string array) // (objects | { value: map_values(decode_ddb) }) # all other non-conforming objects // (arrays | { value: map(decode_ddb) }) # all other non-conforming arrays // { value: . }).value # everything else ; 

 $ jq 'decode_ddb' input.json { "Count": 1, "Items": [ { "Id": "app1", "Parameters": { "nfs#IP": "192.17.0.13", "maxCount": 1, "nfs#defaultPath": "/mnt/ebs/" } } ], "ScannedCount": 1, "ConsumedCapacity": null } 

Answer 2

As far as I know, there is no other way out, such as "detailed", which you published. So I think you can’t avoid intermediate tools like jq oder sed

There are several suggestions in this post for converting raw dynamo data:

Export data from DynamoDB

Perhaps you can adapt one of these scenarios in conjunction with jq or sed

Answer 3

Another way to achieve this is to use the node.js extension, such as node-dynamodb or dynamodb-marshaler and create a node command-line tool.

An interesting guide to creating a node.js command-line application with a commander package : Creating the first Node.js command-line application

Answer 4

Here is another approach. It may be a little cruel, but it shows the basic idea.

 def unwanted: ["B","BOOL","M","S","L","BS","SS"]; def fixpath(p): [ p[] | select( unwanted[[.]]==[] ) ]; def fixnum(p;v): if p[-2]=="NS" then [p[:-2]+p[-1:],(v|tonumber)] elif p[-1]=="N" then [p[:-1], (v|tonumber)] else [p,v] end; reduce (tostream|select(length==2)) as [$p,$v] ( {} ; fixnum(fixpath($p);$v) as [$fp,$fv] | setpath($fp;$fv) ) 

Try it now!

Run example (assuming the filter is in filter.jq and the data in data.json )

 $ jq -M -f filter.jq data.json { "ConsumedCapacity": null, "Count": 1, "Items": [ { "Id": "app1", "Oldtypes": { "typeBS": [ "VGVybWluYXRvcgo=", "VGVybWluYXRvciAyOiBKdWRnbWVudCBEYXkK", "VGVybWluYXRvciAzOiBSaXNlIG9mIHRoZSBNYWNoaW5lcwo=", "VGVybWluYXRvciA0OiBTYWx2YXRpb24K", "VGVybWluYXRvciA1OiBHZW5lc2lzCg==" ], "typeNS": [ 0, 1, 2, 3, 4, 5 ], "typeSS": [ "foo", "bar", "baz" ] }, "Parameters": { "nfs": { "IP": "172.16.0.178", "activated": true, "defaultPath": "/mnt/ebs/", "key": "dGhpcyB0ZXh0IGlzIGJhc2U2NC1lbmNvZGVk" }, "ws": { "number": 5, "values": [ "12253456346346", "23452353463464", "23523453461232", "34645745675675", "46456745757575" ] } } } ], "ScannedCount": 1 }