Convert JSON to vertical table

Question

I have the below payload, and what I am trying to produce is a horizontal column output like such, with a newline between entries. Does someone know how this can be achieved? Either in jq directly or with some interesting bash. :

StackId              : arn:aws:cloudformation:us-east-1:882038671278:stack/cbongiorno-30800-bb-lambda/97b14e40-75a3-11ea-bb77-0e8a861a6983
EventId              : 97b2fbf0-75a3-11ea-bb77-0e8a861a6983
StackName            : cbongiorno-30800-bb-lambda
LogicalResourceId    : cbongiorno-30800-bb-lambda
PhysicalResourceId   : arn:aws:cloudformation:us-east-1:882038671278:stack/cbongiorno-30800-bb-lambda/97b14e40-75a3-11ea-bb77-0e8a861a6983
ResourceType         : AWS::CloudFormation::Stack
Timestamp            : 2020-04-03T12:06:47.501Z
ResourceStatus       : CREATE_IN_PROGRESS
ResourceStatusReason : User Initiated

EventId              : BBPassword-CREATE_IN_PROGRESS-2020-04-03T12:06:51.336Z
StackName            : cbongiorno-30800-bb-lambda
LogicalResourceId    : BBPassword
PhysicalResourceId   : 
ResourceType         : AWS::SSM::Parameter
Timestamp            : 2020-04-03T12:06:51.336Z
ResourceStatus       : CREATE_IN_PROGRESS

Here is the 2 commands I am using to produce output but neither is ideal.

• I have deleted a key that's usually filled with JSON and it just messes everything up.
• The first example I insert a delimiter that I am hoping I can use later to strip out
• The second example gives me an error xargs: unterminated quote:
• In both cases I hardcoded the format length. But for the curious, it can be done as such: jq -re '.StackEvents | map(to_entries | map(.key | length) | max) | max'

jq -re '.StackEvents | .[] | del(.ResourceProperties) | . * {"entry":"---"} | to_entries | .[] | "\(.key) \"\(.value?)\""' bin/logs/3.json  | xargs -n 2 printf "%-21s: %s\n"

jq -re '.StackEvents | .[] | del(.ResourceProperties) | . * {"":"\n"} | to_entries | .[] | "\(.key) \"\(.value?)\""' bin/logs/3.json  | xargs -n 2 printf "%-21s: %s\n"

Here is the payload:

{
    "StackEvents": [
        {
            "StackId": "arn:aws:cloudformation:us-east-1:882038671278:stack/cbongiorno-30800-bb-lambda/97b14e40-75a3-11ea-bb77-0e8a861a6983",
            "EventId": "BBWebhookLogGroup-CREATE_IN_PROGRESS-2020-04-03T12:06:51.884Z",
            "StackName": "cbongiorno-30800-bb-lambda",
            "LogicalResourceId": "BBWebhookLogGroup",
            "PhysicalResourceId": "cbongiorno-30800-bb-lambda",
            "ResourceType": "AWS::Logs::LogGroup",
            "Timestamp": "2020-04-03T12:06:51.884Z",
            "ResourceStatus": "CREATE_IN_PROGRESS",
            "ResourceStatusReason": "Resource creation Initiated",
            "ResourceProperties": "{\"RetentionInDays\":\"7\",\"LogGroupName\":\"cbongiorno-30800-bb-lambda\"}"
        },
        {
            "StackId": "arn:aws:cloudformation:us-east-1:882038671278:stack/cbongiorno-30800-bb-lambda/97b14e40-75a3-11ea-bb77-0e8a861a6983",
            "EventId": "BBUserName-CREATE_IN_PROGRESS-2020-04-03T12:06:51.509Z",
            "StackName": "cbongiorno-30800-bb-lambda",
            "LogicalResourceId": "BBUserName",
            "PhysicalResourceId": "",
            "ResourceType": "AWS::SSM::Parameter",
            "Timestamp": "2020-04-03T12:06:51.509Z",
            "ResourceStatus": "CREATE_IN_PROGRESS",
            "ResourceProperties": "{\"Type\":\"String\",\"Description\":\"The username for this lambda to operate under\",\"Value\":\"chb0bitbucket\",\"Name\":\"/bb-webhooks/authorization/username\"}"
        },
        {
            "StackId": "arn:aws:cloudformation:us-east-1:882038671278:stack/cbongiorno-30800-bb-lambda/97b14e40-75a3-11ea-bb77-0e8a861a6983",
            "EventId": "BBWebhookLogGroup-CREATE_IN_PROGRESS-2020-04-03T12:06:51.409Z",
            "StackName": "cbongiorno-30800-bb-lambda",
            "LogicalResourceId": "BBWebhookLogGroup",
            "PhysicalResourceId": "",
            "ResourceType": "AWS::Logs::LogGroup",
            "Timestamp": "2020-04-03T12:06:51.409Z",
            "ResourceStatus": "CREATE_IN_PROGRESS",
            "ResourceProperties": "{\"RetentionInDays\":\"7\",\"LogGroupName\":\"cbongiorno-30800-bb-lambda\"}"
        },
        {
            "StackId": "arn:aws:cloudformation:us-east-1:882038671278:stack/cbongiorno-30800-bb-lambda/97b14e40-75a3-11ea-bb77-0e8a861a6983",
            "EventId": "BBPassword-CREATE_IN_PROGRESS-2020-04-03T12:06:51.336Z",
            "StackName": "cbongiorno-30800-bb-lambda",
            "LogicalResourceId": "BBPassword",
            "PhysicalResourceId": "",
            "ResourceType": "AWS::SSM::Parameter",
            "Timestamp": "2020-04-03T12:06:51.336Z",
            "ResourceStatus": "CREATE_IN_PROGRESS",
            "ResourceProperties": "{\"Type\":\"String\",\"Description\":\"The password for this lambda to operate under with BB. Unfortunately, using an encrypted password is currently not possible\",\"Value\":\"****\",\"Name\":\"/bb-webhooks/authorization/password\"}"
        },
        {
            "StackId": "arn:aws:cloudformation:us-east-1:882038671278:stack/cbongiorno-30800-bb-lambda/97b14e40-75a3-11ea-bb77-0e8a861a6983",
            "EventId": "97b2fbf0-75a3-11ea-bb77-0e8a861a6983",
            "StackName": "cbongiorno-30800-bb-lambda",
            "LogicalResourceId": "cbongiorno-30800-bb-lambda",
            "PhysicalResourceId": "arn:aws:cloudformation:us-east-1:882038671278:stack/cbongiorno-30800-bb-lambda/97b14e40-75a3-11ea-bb77-0e8a861a6983",
            "ResourceType": "AWS::CloudFormation::Stack",
            "Timestamp": "2020-04-03T12:06:47.501Z",
            "ResourceStatus": "CREATE_IN_PROGRESS",
            "ResourceStatusReason": "User Initiated"
        }
    ]
}

Based on input from others, I have put together a simple bash script illustrating a tiny anomaly (the column width isn't uniform):

#!/usr/bin/env bash
set -e
set -o pipefail

fileCount=$(( $( ls -1 logs/*.json | wc -l) - 1))
for i in $(seq 1 $fileCount); do
    jq -rs '
      def width:      map(keys_unsorted | map(length) | max) | max ;
      def pad($w):    . + (($w-length)*" ") ;

      .[1].StackEvents - .[0].StackEvents | sort_by (.Timestamp) 
      | width as $w | map(to_entries | map("\(.key|pad($w)) : \(.value)"), [""]) 
      | .[][]
    ' "logs/$((i - 1)).json" "logs/$i.json"

done

Yields:

StackId              : arn:aws:cloudformation:us-east-1:882038671278:stack/cbongiorno-30800-bb-lambda/97b14e40-75a3-11ea-bb77-0e8a861a6983
EventId              : ApiKey-CREATE_COMPLETE-2020-04-03T12:07:47.382Z
StackName            : cbongiorno-30800-bb-lambda
LogicalResourceId    : ApiKey
PhysicalResourceId   : KYgzCNAzPw5Tsy3dKBdoTaHlxywijTSrb1d2UIQ2
ResourceType         : AWS::ApiGateway::ApiKey
Timestamp            : 2020-04-03T12:07:47.382Z
ResourceStatus       : CREATE_COMPLETE
ResourceProperties   : {"StageKeys":[{"StageName":"beta","RestApiId":"8n6tijwaib"}]}

StackId            : arn:aws:cloudformation:us-east-1:882038671278:stack/cbongiorno-30800-bb-lambda/97b14e40-75a3-11ea-bb77-0e8a861a6983
EventId            : bc9371c0-75a3-11ea-b442-1217092af407
StackName          : cbongiorno-30800-bb-lambda
LogicalResourceId  : cbongiorno-30800-bb-lambda
PhysicalResourceId : arn:aws:cloudformation:us-east-1:882038671278:stack/cbongiorno-30800-bb-lambda/97b14e40-75a3-11ea-bb77-0e8a861a6983
ResourceType       : AWS::CloudFormation::Stack
Timestamp          : 2020-04-03T12:07:49.203Z
ResourceStatus     : CREATE_COMPLETE

Answer 1

Here is a solution with some helper functions that can be generalized for other uses.

def width:      map(keys | map(length) | max) | max ;
def pad($w):    . + (($w-length)*" ") ;

  .StackEvents
| width as $w
| map(del(.ResourceProperties) | to_entries | map("\(.key|pad($w)) : \(.value)"), [""])
| .[][]

It should produce the desired output if jq is passed -r

Try it online!

EDIT: as peak and oguz ismail point out in the comments this solution could be improved using keys_unsorted and should exclude .ResourceProperties from the width calculation.
Here is version with those improvements:

def width:      map(keys_unsorted | map(length) | max) | max ;
def pad($w):    . + (($w-length)*" ") ;

  .StackEvents
| map(del(.ResourceProperties))
| width as $w
| map(to_entries | map("\(.key|pad($w)) : \(.value)"), [""])
| .[][]

Try it online!

Answer 2

Here is a solution that:

• uses max/1 for efficiency

• addresses some of the issues with calculating the "width" of Unicode strings, e.g. if we want the "width" of:

  "J̲o̲s̲é̲" to be calculated as 4

Note that the jq filter grapheme_length as defined here ignores issues with control characters and zero-width spaces.

Generic functions

def max(stream):
  reduce stream as $x (null; if . == null then $x elif $x > . then $x else . end);

# Grapheme Length ignoring issues with control characters
# Mn = non-spacing mark
# Mc = combining
# Cf = soft-hyphen, bidi control characters, and language tag characters
def grapheme_length:
  gsub("\\p{Mn}";"") | gsub("\\p{Mc}";"") | gsub("\\p{Cf}";"")
  | length;

def pad($w): tostring + (($w - grapheme_length)*" ") ;

Main program

.StackEvents
| max(.[]
      | keys_unsorted[] 
      | select(. != "ResourceProperties") 
      | grapheme_length) as $w
| map(del(.ResourceProperties)
      | to_entries
      | map("\(.key|pad($w)) : \(.value)"), [""])
| .[][]

Answer 3

JQ doesn't have a builtin for padding strings but it's not that hard to implement that functionality. Given -r/--raw-output option on the command line, below script will produce your desired output.

.StackEvents
| map(del(.ResourceProperties))
| ( [ .[] | keys_unsorted[] ]
    | map(length)
    | max + 1
  ) as $max
| .[]
  | ( keys_unsorted as $keys
    | [ $keys,
      ( $keys
        | map(length)
        | map($max - .)
        | map(. * " " + ": ")
      ),
      map(.)
    ]
    | transpose[]
    | add
  ),
  ""

^{Online demo}