Converting JSON into CSV data for Google Sheets

Like many people, I use Google Sheets to quickly create and share tabular data. As well as creating spreadsheets by pasting results generated in psql, I often create reports from JSON files using JQ. This post is a note-to-self on how to do this.

Here’s a command to create a tab-separated report from a JSON events file exported from Loggly:

$ cat loggly_events.json | \ 
    jq -r '.events[].event.json | 
           select(.params | has("payment_day")) | 
           [.timestamp, .account, .params.payment_day] | 
           @tsv' | clipboard

Note:

  • The -r option instructs JQ to output raw strings, not quoted JSON strings.

  • @tsv is a JQ format string for outputting tab-separated values.

  • The clipboard command is an alias for pbcopy, the system clipboard on OS-X.

----

Something wrong? Suggest an improvement or add a comment (see article history)
Tagged with: jq, loggly
Filed in: tips

Previous: Podcast.__init__ on Oscar
Next: Git tips for working with pull requests

Copyright © 2005-2017 David Winterbottom
Content licensed under CC BY-NC-SA 4.0.