Use JQ to make a CSVs out of Trello Export output, with additional columns for repeating labels

jq_trello_exports.md

Un-normalised output:

(reduce .lists[] as $list ({}; .[$list.id] = $list.name)) as $lists | 
.cards[] | 
select(.closed != true) | 
[$lists[.idList], .name, .labels[].name]) | 
@csv

Normalised output:

["List","Card","Label"], 
((reduce .lists[] as $list ({}; .[$list.id] = $list.name)) as $lists | 
.cards[] | 
select(.closed != true) | 
{list: $lists[.idList], card: .name, labels: .labels[].name} | 
[.list, .card, .labels]) | 
@csv

What I learnt using JQ

References and tutorials

https://stedolan.github.io/jq/manual/

https://jqplay.org/

Doing an operation on trello output

• Basic selection with .element

• Pipe does what it always does

• Taking apart the following:

  (["List", "Card", "Labels"], (reduce .lists[] as $list ({}; .[$list.id] = $list.name)) as $lists | (reduce .labels[] as $labell ({}; .[$labell.id] = $labell.name)) as $labels | .cards[] | select(.closed != true) | [$lists[.idList],.name, $labels[.labels[].id]]) | @csv

• ["List", "Card", "Labels"] - print these three values as a single record. This is evaluated against the JSON input, but contains no dot-references, so therefore only the static text is returned.

• , - move to the next output

• (reduce .lists[] as $list ({}; .[$list.id] = $list.name)) as $lists - take the .lists object and reduce its contents down to a single key-value pair. Finally, put the result in the $lists variable, rather than returning it. The variable is stored, and the whole JSON input is passed on.

• (reduce .labels[] as $labell ({}; .[$labell.id] = $labell.name)) as $labels - Do the same with labels. The variable is stored, and the whole JSON input is passed on.

• .cards[] - filter out everything but the cards object

• select(.closed != true) - filter out the closed objects

• [$lists[.idList],.name, $labels[.labels[].id]] - now produce the final output filter. The list name (looked up from the $lists array variable), the card name, and all the labels recursively returned (looked up from the $labels array variable).

• All of that is filtered down as a list of arrays. We put it all in parentheses, so it can be piped to the next command as one.

• @csv - turn the list of arrays (JSON format) into a CSV format