How to simplify aws DynamoDB query JSON output from the command line?
amazon-dynamodb, amazon-web-services, aws-cli, jq, json
Solution
You can decode the values recursively with a well crafted function. It looks like the key names correspond to a type:
S -> string
N -> number
M -> map
Handle each of the cases you want to decode if possible, otherwise filter it out. You can make use of the various type filters and the alternative operator to do so.
$ 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("NULL") | null }) # null (from boolean)
// (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
}
Problem
I'm working with The AWS Command Line Interface for DynamoDB. When we query an item, we get a very detailed JSON output. You get something like this (it has been built from the `get-item` in order to be almost exhaustive (the `NULL` type has been 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 } ``` Is there any 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 } ``` There is nothing helpful in the dynamodb - AWS CLI 1.7.10 documentation. We must get the result from the command line. I'm willing to use other command line tools like `jq` if necessary, but such a `jq` mapping appears to complicated to me. Update 1: `jq` based solution (with help from DanielH's answer) With `jq` it 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' ``` Result will be: `172.16.0.178` The `jq` `-r` option gives you a raw output. Update 2: `jq` based solution (with help from @jeff-mercado) Here is an updated and commented version of Jeff Mercado `jq` function to unmarshall DynamoDB output. It will give you the expected output: ``` $> 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, lets say `ddb-query-result.json`, you can execute to get desired result: ``` $> jq -f unmarshal_dynamodb.jq ddb-query-result.json ```