What are good CLI tools for JSON?

Question

a common problem

Although I may be diagnosing the root cause of the event, determining how many users have affected it, or extracting the synchronization logs to assess the impact of the recent code change on performance and throughput, my tools remain grep : grep , awk , sed , tr , uniq , sort , zcat , tail , head , join and split . To glue them all together, Unix gives us channels, and for more xargs filtering we have xargs . If they me perl -e , there is always perl -e .

These tools are ideal for processing CSV files, tab delimited files, predictable line format log files, or comma-separated key-value files. In other words, files in which each line has no context.

XML Analogs

Recently, I needed to scroll through gigabytes of XML in order to build a histogram of user usage. It was simple enough with the tools that I had, but for more complex queries, normal approaches do not work. Let's say I have files with these elements:

 <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 make a comparison between the user and the average <baz> on <foo> . Processing line by line is no longer an option: I need to know which user <foo> I am currently checking to know whose average value to update. Any kind of Unix one liner that performs this task is likely to be incomprehensible.

Fortunately, in the XML field, we have great technologies like XPath, XQuery, and XSLT that can help us.

I used to use the wonderful Perl XML::XPath module to execute queries like the one above, but after finding the TextMate plugin that can run an XPath expression for my current window , I stopped writing one-time Perl scripts to request XML. And I just found out about XMLStarlet, which is installed when typing this text and which I look forward to in the future.

JSON solutions?

So this leads me to my question: are there any such tools for JSON? It’s just a matter of time before some research task will require me to perform similar requests to JSON files, and without tools like XPath and XSLT, such a task would be much more complicated. If I had a JSON bundle that would look 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 I wanted to know the average number of phone numbers each person had, I could do something like this with XPath:

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

Questions

1. Are there any command line tools that can “request” JSON files this way?
2. If you need to process a bunch of JSON files on a Unix command line, what tools do you use?
3. Damn, is there any work on creating such a query language for JSON?
4. If you use such tools in your daily work, what do you like / dislike about them? Are there any bugs?

I note that more and more data serialization is performed using JSON, so such processing tools will be critical in analyzing large data dumps in the future. The language libraries for JSON are very strong, and it’s easy enough to write scripts for such processing, but in order for people to really be able to play with data shell tools, they are necessary.

Related issues

• Equivalent to Grep and Sed for XML Command Line Processing
• Is there a query language for JSON?
• JSONPath or another XPath-like utility for JSON / Javascript; or jquery json

Answer 1

I just found this:

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

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

2014 update:

@ user456584 mentioned:

There is also a 'json' command (e.g. 'jsontool'). I tend to prefer this to JQ. Very UNIX. Here is the project link: github.com/trentm/json -

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

• JQ : http://stedolan.imtqy.com/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

Answer 2

I created a module specifically designed to manipulate JSON with the command line:

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-fledged Javascript command.
• POWERFUL - provides the full power and functionality of underscore.js (plus underscore.string)
• SIMPLE - makes it easy to write single-line JS similar to using "perl -pe"
• CHAINED . Several command commands can be linked together to create a data processing pipeline.
• MULTI-FORMAT . Rich support for I / O formats - pretty printed, strict JSON, etc. [soon]
• DOCUMENTED - excellent command line documentation with several examples for each command

This allows you to do powerful things very 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 to use; however, I am still creating a few functions, such as alternatives for the I / O and merge formats in my template processing tool (see TODO.md). If you have any feature requests, comment on this post or add a problem to github. I have developed a fairly extensive range of functions, but I would be happy to give priority to the functions that are necessary for members of the community.

Answer 3

One way to do this is to convert it to XML. The following uses two perl modules (JSON and XML :: Simple) to convert fly-by:

 cat test.json | perl -MJSON -MXML::Simple -e 'print XMLout(decode_json(do{local$/;<>}),RootName=>"json")' 

which for your json example ends up like:

 <json age="200" firstName="Bender" lastName="Robot"> <address city="New York" postalCode="1729" state="NY" streetAddress="123" /> <phoneNumber number="666 555-1234" type="home" /> <phoneNumber number="666 555-4567" type="fax" /> </json> 

Answer 4

Take a look at this crazy jsawk project. This is a design for filtering through JSON input from the command line. Check out resty , as well as for a command line REST client that can be used in pipelines that may come in handy.

Answer 5

I recently discovered that JSON can easily be eval -ed with Python:

 $ python -c "json=eval(open('/json.txt').read()); print len(json['phoneNumber'])" 2 

Although this method will obviously fail if the JSON input contains zeros.

Answer 6

See f:json-document() from the FXSL 2.x Library .

Using this function is very easy to enable JSon and use it in the same way as ... XML.

For example, you can simply write the following XPath expression:

 f:json-document($vstrParam)/Students/*[sex = 'Female'] 

and get all the children of Students with sex = 'Female'

Here is a complete example:

 <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:f="http://fxsl.sf.net/" exclude-result-prefixes="f xs" > <xsl:import href="../f/func-json-document.xsl"/> <xsl:output omit-xml-declaration="yes" indent="yes"/> <xsl:variable name="vstrParam" as="xs:string"> { "teacher":{ "name": "Mr Borat", "age": "35", "Nationality": "Kazakhstan" }, "Class":{ "Semester": "Summer", "Room": null, "Subject": "Politics", "Notes": "We're happy, you happy?" }, "Students": { "Smith": {"First Name":"Mary","sex":"Female"}, "Brown": {"First Name":"John","sex":"Male"}, "Jackson": {"First Name":"Jackie","sex":"Female"} } , "Grades": { "Test": [ {"grade":"A","points":68,"grade":"B","points":25,"grade":"C","points":15}, {"grade":"C","points":2, "grade":"B","points":29, "grade":"A","points":55}, {"grade":"C","points":2, "grade":"A","points":72, "grade":"A","points":65} ] } } </xsl:variable> <xsl:template match="/"> <xsl:sequence select= "f:json-document($vstrParam)/Students/*[sex = 'Female']"/> </xsl:template> </xsl:stylesheet> 

When the specified conversion is applied to any XML document (ignored), the correct result is obtained :

 <Smith> <First_Name>Mary</First_Name> <sex>Female</sex> </Smith> <Jackson> <First_Name>Jackie</First_Name> <sex>Female</sex> </Jackson> 

Answer 7

There is also an interactive terminal tool - FX

Pass any JSON and anonymous function to FX to reduce it.

 $ echo '{...}' | fx [code ...] 

Run interactive mode without passing arguments:

 $ curl ... | fx 

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

Answer 8

Fortunately, in the XML field, we have great technologies like XPath, XQuery, and XSLT that can help us.
[...]
So this leads me to my question: are there any such tools for JSON?

If you ask me, Xidel is exactly what you are looking for.

Xidel is a command-line tool for loading and extracting data from HTML / XML or JSON-API pages using CSS, XPath 3.0, XQuery 3.0, JSONiq, or template templates. It can also create new or converted XML / HTML / JSON documents.

Inquiry:

 xidel -s "input.json" \ -e ' $json/avg( count( (phoneNumber)() ) ) ' 

or

 xidel -s "input.json" -e '$json/avg(count((phoneNumber)()))' 

Exit:

 2