JSON Tables in the Terminal

The other day I was looking around for a simple command-line tool to print JSON and JSON Lines data to a table in the terminal. I found a few programs that can do it with some massaging of the data, like visidata, jt, and json-table, but these really didn’t meet my requirements.

I wanted to pipe JSON or JSON Lines data into a program and get a nicely formatted table with correct headers without any additional configuration or arguments. I also wanted it to automatically fit the terminal width and wrap or truncate the columns to fit the data with no complicated configuration. Basically, I just wanted it to “do the right thing” so I can view JSON data in a tabular format without any fuss.

I ended up creating a little command-line utility called jtbl that does exactly that:

$ cat cities.json | jtbl 
  LatD    LatM    LatS  NS      LonD    LonM    LonS  EW    City               State
------  ------  ------  ----  ------  ------  ------  ----  -----------------  -------
    41       5      59  N         80      39       0  W     Youngstown         OH
    42      52      48  N         97      23      23  W     Yankton            SD
    46      35      59  N        120      30      36  W     Yakima             WA
    42      16      12  N         71      48       0  W     Worcester          MA
    43      37      48  N         89      46      11  W     Wisconsin Dells    WI
    36       5      59  N         80      15       0  W     Winston-Salem      NC
    49      52      48  N         97       9       0  W     Winnipeg           MB

jtbl is simple and elegant. It just takes in piped JSON or JSON Lines data and prints a table. There’s only one option to turn on column truncation vs. wrapping columns if the terminal width is too narrow to display the complete table.

$ jtbl -h
jtbl:   Converts JSON and JSON Lines to a table

Usage:  <JSON Data> | jtbl [OPTIONS]

        -t  truncate data instead of wrapping if too long for the terminal width
        -v  version info
        -h  help

Here’s an example using a relatively slim terminal width of 75:

$ jc dig www.cnn.com | jq '.[].answer' | jtbl 
+----------------+---------+--------+-------+----------------+
| name           | class   | type   |   ttl | data           |
+================+=========+========+=======+================+
| www.cnn.com.   | IN      | CNAME  |   143 | turner-tls.map |
|                |         |        |       | .fastly.net.   |
+----------------+---------+--------+-------+----------------+
| turner-tls.map | IN      | A      |     4 | 151.101.1.67   |
| .fastly.net.   |         |        |       |                |
+----------------+---------+--------+-------+----------------+
| turner-tls.map | IN      | A      |     4 | 151.101.129.67 |
| .fastly.net.   |         |        |       |                |
+----------------+---------+--------+-------+----------------+
| turner-tls.map | IN      | A      |     4 | 151.101.65.67  |
| .fastly.net.   |         |        |       |                |
+----------------+---------+--------+-------+----------------+
| turner-tls.map | IN      | A      |     4 | 151.101.193.67 |
| .fastly.net.   |         |        |       |                |
+----------------+---------+--------+-------+----------------+

or with truncation enabled:

$ jc dig www.cnn.com | jq '.[].answer' | jtbl -t 
name                  class    type      ttl  data
--------------------  -------  ------  -----  --------------------
www.cnn.com.          IN       CNAME      48  turner-tls.map.fastl
turner-tls.map.fastl  IN       A          13  151.101.129.67
turner-tls.map.fastl  IN       A          13  151.101.65.67
turner-tls.map.fastl  IN       A          13  151.101.193.67
turner-tls.map.fastl  IN       A          13  151.101.1.67

Here’s an example using it to print the result of an XML API query response, converted to JSON with jc, and filtered with jq:

$ curl -X GET --basic -u "testuser:testpassword" https://reststop.randomhouse.com/resources/works/19306 | jc --xml | jq '.work' | jtbl
+----------+----------+----------+------------+------------+----------+------------+------------+------------+------------+
| titles   |   workid | @uri     | authorwe   | onsaleda   | series   | titleAut   | titleSub   | titlesho   | titleweb   |
|          |          |          | b          | te         |          | h          | titleAut   | rt         |            |
|          |          |          |            |            |          |            | h          |            |            |
+==========+==========+==========+============+============+==========+============+============+============+============+
|          |    19306 | https:// | BROWN, D   | 2003-09-   | ROBERT L | Angels &   | Angels &   | ANGELS &   | Angels &   |
|          |          | reststop | AN         | 02T00:00   | ANGDON   |  Demons    |  Demons    |  DEMON(L   |  Demons    |
|          |          | .randomh |            | :00-04:0   |          | : Dan Br   | :  : Dan   | PTP)(REI   |            |
|          |          | ouse.com |            | 0          |          | own        |  Brown     | )(MTI)     |            |
|          |          | /resourc |            |            |          |            |            |            |            |
|          |          | es/works |            |            |          |            |            |            |            |
|          |          | /19306   |            |            |          |            |            |            |            |
+----------+----------+----------+------------+------------+----------+------------+------------+------------+------------+

Again, with truncation enabled:

$ curl -X GET --basic -u "testuser:testpassword" https://reststop.randomhouse.com/resources/works/19306 | jc --xml | jq '.work' | jtbl -t
authorweb    titles      workid  @uri        onsaledate    series      titleAuth    titleSubti    titleshort    titleweb
-----------  --------  --------  ----------  ------------  ----------  -----------  ------------  ------------  ----------
BROWN, DAN                19306  https://re  2003-09-02    ROBERT LAN  Angels & D   Angels & D    ANGELS & D    Angels & D

I found that having the ability to quickly see the JSON data in a tabular, horizontal format can sometimes help me visualize ‘where I am’ in the data more easily than looking at long vertical lists of JSON.

I hope you enjoy it!

Published by kellyjonbrazil

I'm a cybersecurity and cloud computing nerd.

6 thoughts on “JSON Tables in the Terminal

  1. jtbl version 1.1.1 now supports manual column configuration and disabling of wrapping and truncation:

    
    $ jtbl -h
    jtbl:   Converts JSON and JSON Lines to a table
    
    Usage:   | jtbl [OPTIONS]
    
        --cols=n   manually configure the terminal width
        -n         do not try to wrap if too long for the terminal width        
        -t         truncate data instead of wrapping if too long for the terminal width
        -v         version info
        -h         help
    

Leave a Reply

RSS
Follow by Email
LinkedIn
Share
%d bloggers like this: