[SOLVED] How to format a JSON string as a table using jq?

Issue

Just started out with Bash scripting and stumbled upon jq to work with JSON.

I need to transform a JSON string like below to a table for output in the terminal.

[{
    "name": "George",
    "id": 12,
    "email": "[email protected]"
}, {
    "name": "Jack",
    "id": 18,
    "email": "[email protected]"
}, {
    "name": "Joe",
    "id": 19,
    "email": "[email protected]"
}]

What I want to display in the terminal:

ID        Name
=================
12        George
18        Jack
19        Joe

Notice how I don't want to display the email property for each row, so the jq command should involve some filtering. The following gives me a plain list of names and id's:

list=$(echo "$data" | jq -r '.[] | .name, .id')
printf "$list"

The problem with that is, I cannot display it like a table. I know jq has some formatting options, but not nearly as good as the options I have when using printf. I think I want to get these values in an array which I can then loop through myself to do the formatting...? The things I tried give me varying results, but never what I really want.

Can someone point me in the right direction?

Solution

Why not something like :

echo '[{
    "name": "George",
    "id": 12,
    "email": "[email protected]"
}, {
    "name": "Jack",
    "id": 18,
    "email": "[email protected]"
}, {
    "name": "Joe",
    "id": 19,
    "email": "[email protected]"
}]' | jq -r '.[] | "\(.id)\t\(.name)"'

Output

12  George
18  Jack
19  Joe

---

Edit 1 : For fine grained formatting use tools like awk

 echo '[{
    "name": "George",
    "id": 12,
    "email": "[email protected]"
}, {
    "name": "Jack",
    "id": 18,
    "email": "[email protected]"
}, {
    "name": "Joe",
    "id": 19,
    "email": "[email protected]"
}]' | jq -r '.[] | [.id, .name] | @csv' | awk -v FS="," 'BEGIN{print "ID\tName";print "============"}{printf "%s\t%s%s",$1,$2,ORS}'
ID  Name
============
12  "George"
18  "Jack"
19  "Joe"

---

Edit 2 : In reply to

There's no way I can get a variable containing an array straight from jq?

Why not?

A bit involved example( in fact modified from yours ) where email is changed to an array demonstrates this

echo '[{
    "name": "George",
    "id": 20,
    "email": [ "[email protected]" , "[email protected]" ]
}, {
    "name": "Jack",
    "id": 18,
    "email": [ "[email protected]" , "[email protected]" ]
}, {
    "name": "Joe",
    "id": 19,
    "email": [ "[email protected]" ]
}]' | jq -r '.[] | .email'

Output

[
  "[email protected]",
  "[email protected]"
]
[
  "[email protected]",
  "[email protected]"
]
[
  "[email protected]"
]

Answered By - sjsam