Amazon DynamoDB Formatted JSON using JQ

Linux sed is useful tool to format and transform plain text. But sed is not so useful for structured data like JSON. JQ is a sed-like tool easily deal with JSON.

./jq

is a lightweight and flexible command-line JSON processor.

Install jq on OS X:
brew install jq

Install jq on Ubuntu:
apt-get install jq

Others:
https://stedolan.github.io/jq/download/

UserPoints.json data exported from Amazon DynamoDB

# AWS_ACCESS_KEY_ID=XXXXXXXXXXXXXXXXXXXXX AWS_SECRET_ACCESS_KEY=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX /usr/local/bin/aws dynamodb scan –table-name UserPoints –region us-east-1 –output json > UserPoints.json

# jq “[.Items[] | { userId: .userId.S, CompanyCode: .CompanyCode.S, date: .date.S, points: .points.S, Id: .Id.S }]” UserPoints.json

It will output like this…

[
{
“userId”: “user1@gmail.com”,
“CompanyCode”: “ABC107”,
“date”: “2017/04/08”,
“point”: “16”,
“Id”: “1496238999”
},
{
“userId”: “user2@gmail.com”,
“CompanyCode”: “ABC107”,
“date”: “2017/02/07”,
“point”: “45”,
“Id”: “1496239218”
}
]