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-2024 David Winterbottom
Content licensed under CC BY-NC-SA 4.0.