What are good CLI tools for JSON?

General Problem

Though I may be diagnosing the root cause of an event, determining how many users it affected, or distilling timing logs in order to assess the performance and throughput impact of a recent code change, my tools stay the same: grep, awk, sed, tr, uniq, sort, zcat, tail, head, join, and split. To glue them all together, Unix gives us pipes, and for fancier filtering we have xargs. If these fail me, there's always perl -e.

These tools are perfect for processing CSV files, tab-delimited files, log files with a predictable line format, or files with comma-separated key-value pairs. In other words, files where each line has next to no context.

XML Analogues

I recently needed to trawl through Gigabytes of XML to build a histogram of usage by user. This was easy enough with the tools I had, but for more complicated queries the normal approaches break down. Say I have files with items like this:

<foo user="me">
    <baz key="zoidberg" value="squid" />
    <baz key="leela"    value="cyclops" />
    <baz key="fry"      value="rube" />
</foo>

And let's say I want to produce a mapping from user to average number of <baz>s per <foo>. Processing line-by-line is no longer an option: I need to know which user's <foo> I'm currently inspecting so I know whose average to update. Any sort of Unix one liner that accomplishes this task is likely to be inscrutable.

Fortunately in XML-land, we have wonderful technologies like XPath, XQuery, and XSLT to help us.

Previously, I had gotten accustomed to using the wonderful XML::XPath Perl module to accomplish queries like the one above, but after finding a TextMate Plugin that could run an XPath expression against my current window, I stopped writing one-off Perl scripts to query XML. And I just found out about XMLStarlet which is installing as I type this and which I look forward to using in the future.

JSON Solutions?

So this leads me to my question: are there any tools like this for JSON? It's only a matter of time before some investigation task requires me to do similar queries on JSON files, and without tools like XPath and XSLT, such a task will be a lot harder. If I had a bunch of JSON that looked like this:

{
  "firstName": "Bender",
  "lastName": "Robot",
  "age": 200,
  "address": {
    "streetAddress": "123",
    "city": "New York",
    "state": "NY",
    "postalCode": "1729"
  },
  "phoneNumber": [
    { "type": "home", "number": "666 555-1234" },
    { "type": "fax", "number": "666 555-4567" }
  ]
}

And wanted to find the average number of phone numbers each person had, I could do something like this with XPath:

fn:avg(/fn:count(phoneNumber))

Questions

Are there any command-line tools that can "query" JSON files in this way?
If you have to process a bunch of JSON files on a Unix command line, what tools do you use?
Heck, is there even work being done to make a query language like this for JSON?
If you do use tools like this in your day-to-day work, what do you like/dislike about them? Are there any gotchas?

I'm noticing more and more data serialization is being done using JSON, so processing tools like this will be crucial when analyzing large data dumps in the future. Language libraries for JSON are very strong and it's easy enough to write scripts to do this sort of processing, but to really let people play around with the data shell tools are needed.

Solution 1:

I just found this:

http://stedolan.github.com/jq/

"jq is a lightweight and flexible command-line JSON processor."

2014 update:

@user456584 mentioned:

There's also the 'json' command (e.g. 'jsontool'). I tend to prefer it over jq. Very UNIX-y. Here's a link to the project: github.com/trentm/json –

in the json README at http://github.com/trentm/json there is a long list of similar things

jq: http://stedolan.github.io/jq/

json:select: http://jsonselect.org/

jsonpipe: https://github.com/dvxhouse/jsonpipe

json-command: https://github.com/zpoley/json-command

JSONPath: http://goessner.net/articles/JsonPath/, http://code.google.com/p/jsonpath/wiki/Javascript

jsawk: https://github.com/micha/jsawk

jshon: http://kmkeen.com/jshon/

json2: https://github.com/vi/json2

fx: https://github.com/antonmedv/fx

Solution 2:

I have created a module specifically designed for command-line JSON manipulation:

https://github.com/ddopson/underscore-cli

FLEXIBLE - THE "swiss-army-knife" tool for processing JSON data - can be used as a simple pretty-printer, or as a full-powered Javascript command-line
POWERFUL - Exposes the full power and functionality of underscore.js (plus underscore.string)
SIMPLE - Makes it simple to write JS one-liners similar to using "perl -pe"
CHAINED - Multiple command invokations can be chained together to create a data processing pipeline
MULTI-FORMAT - Rich support for input / output formats - pretty-printing, strict JSON, etc [coming soon]
DOCUMENTED - Excellent command-line documentation with multiple examples for every command

It allows you to do powerful things really easily:

cat earthporn.json | underscore select '.data .title'
# [ 'Fjaðrárgljúfur canyon, Iceland [OC] [683x1024]',
#   'New town, Edinburgh, Scotland [4320 x 3240]',
#   'Sunrise in Bryce Canyon, UT [1120x700] [OC]',
# ...
#   'Kariega Game Reserve, South Africa [3584x2688]',
#   'Valle de la Luna, Chile [OS] [1024x683]',
#   'Frosted trees after a snowstorm in Laax, Switzerland [OC] [1072x712]' ]

cat earthporn.json | underscore select '.data .title' | underscore count
# 25

underscore map --data '[1, 2, 3, 4]' 'value+1'
# prints: [ 2, 3, 4, 5 ]

underscore map --data '{"a": [1, 4], "b": [2, 8]}' '_.max(value)'
# [ 4, 8 ]

echo '{"foo":1, "bar":2}' | underscore map -q 'console.log("key = ", key)'
# key = foo
# key = bar

underscore pluck --data "[{name : 'moe', age : 40}, {name : 'larry', age : 50}, {name : 'curly', age : 60}]" name
# [ 'moe', 'larry', 'curly' ]

underscore keys --data '{name : "larry", age : 50}'
# [ 'name', 'age' ]

underscore reduce --data '[1, 2, 3, 4]' 'total+value'
# 10

It has a very nice command-line help system and is extremely flexible. It is well tested and ready for use; however, I'm still building out a few of the features like alternatives for input/output format, and merging in my template handling tool (see TODO.md). If you have any feature requests, comment on this post or add an issue in github. I've designed out a pretty extensive feature-set, but I'd be glad to prioritize features that are needed by members of the community.