Bookmark of: jq

jq is like sed for JSON data - you can use it to slice and filter and map and transform structured data with the same ease that sed, awk, grep and friends let you play with text.

Combining this with GraphAPI on the command line could make for very quick processing. I was asked at work on how to convert GraphAPI JSON into CSV and this looks a killer

1cat dude.json | jq '.value | map({id,displayName}) | (first | keys) as $keys | map([to_entries[] | .value]) as $rows | $keys,$rows[] | @csv' > dude.csv

I ran a GraphAPI call to get id and displayName for ~50 users and piped that into dude.json. Using the above:

• jq: Thats the command!
• .value: That gets the .value attribute; so top level value field
• |: string commands together
• map({id,displayName}) this tells the processor what fields we're interested in
• (first | keys): This reads the first element of an array (which I passed in from .value | map(...)) and gets the keys for the object returned
• as $keys: Creates a $keys variable and fills it with the previous (the name of the keys of the first element, in this case, "id,displayName"
• map([to_entries[] | .value) goes through all the entries turns them from {"keystring":"keyvalue"} to {"key":"keystring", "value": "keyvalue"}. Very cool.
• as $rows: Yup, new variable
• $keys,$rows[] is a filter combining the keys and values, just delicious for
• @csv outputing as CSV

Szymon Stepniak has an excellent breakdown I used to kickstart thinking, but had to play before I could figure it out further.

Update

I found in my continued working I've had to add a SED line to the end as... it just wasn't quite processing right. It returned me each line wrapped in double quotes, and escaping the internal quotes, turning it into a single column with both fields joined together. I think I have to look at Szymon's work some more.

Update 2

Found it. In my defence it was harder for me to figure out because the json file Szymon was using doesn't exist anymore, so I had to guess what the source was and thus how it was being manipulated. In Szymon's model the fields he was working on were at the top level, so he put the map({..}) first. When I tried that it got confused (telling he the fields don't exist) so I took it out completely - what I needed to do whas put that in place once I'd selected the attribute I was processing (.value). Once jq knew just what I was talking about, it was able to find the id and displayName attributes for mapping through the final bit. Phew.