jq: How to create output in a tabular format from a JSON array-CodePudding

I have JSON as given below.

[
   {
      "users": [
         {
            "name": "alfred",
            "points": 10
         },
         {
            "name": "alice",
            "points": 11
         },
         {
            "name": "emma",
            "points": 33
         }
      ],
      "house": "red"
   },
   {
      "users": [
         {
            "name": "Logan",
            "points": 20
         },
         {
            "name": "Keith",
            "points": 19
         },
         {
            "name": "Lisa",
            "points": 18
         },
         {
            "name": "Julia",
            "points": 45
         }
      ],
      "house": "blue"
   }
]

How do I create a tabular output as follows? Basically, I need the value of house appear in the corresponding rows.

The desired output table

|Name  |  Points|House|
|alfred|    10  |red  |
|alice |    11  |red  |
|emma  |    33  |red  |
|Logan |    20  |blue |
|Keith |    19  |blue |
|Lisa  |    18  |blue |
|Julia |    45  |blue |

This is what I tried.

% jq '.[]|.users[]| .name' test.json

"alfred"
"alice"
"emma"
"Logan"
"Keith"
"Lisa"
"Julia"

% jq '.[]|.users[]| (.name, .points)' test.json

"alfred"
10
"alice"
11
"emma"
33
"Logan"
20
"Keith"
19
"Lisa"
18
"Julia"
45

Thanks in advance

CodePudding user response：

With your input, the following jq program produces the output shown below:

jq -r '
  def lpad($len): tostring | ($len - length) as $l | (" " * $l)[:$l]   .;
  (["Names","Points", "House"]
   | ., map(length*"_")),
  (.[] | (.users[] | [.name, .points])    [.house]) 
  | map(lpad(8)) | join("|")'

   Names|  Points|   House
   _____|  ______|   _____
  alfred|      10|     red
   alice|      11|     red
    emma|      33|     red
   Logan|      20|    blue
   Keith|      19|    blue
    Lisa|      18|    blue
   Julia|      45|    blue

For a tiny bit more generality at the expense of a tiny bit of complexity:

  def lpad($len): tostring | ($len - length) as $l | (" " * $l)[:$l]   .;
  def max(s): reduce s as $x (-infinite; if $x > . then $x else . end);
    max("House",  .[].house|length) as $hl
  | max("Points", .[].users[].points|tostring|length) as $pl
  | max("Names",  .[].users[].name|length) as $nl
  | [ ("Names" | lpad($nl)), ("Points" | lpad($pl)), ("House"| lpad($hl))],
    [$nl * "_", $pl * "_", $hl * "_"],
    (.[] | ((.users[] | [(.name | lpad($nl)), (.points|lpad($pl))])    [.house | lpad($hl)] ))
  | join("|")

Season to taste.

CodePudding user response：

If it's OK to forego some formatting and decorations, this produces a simple table.

$ jq -r '["Name", "Points", "House"],(.[]|.house as $house|.users[]|[.name, .points, $house])|@tsv' test.json
Name    Points  House
alfred  10  red
alice   11  red
emma    33  red
Logan   20  blue
Keith   19  blue
Lisa    18  blue
Julia   45  blue

Getting rid of the variable assignment, this outputs the same simple table.

$ jq -r '["Name", "Points", "House"],(.[]|(.users[]|[.name, .points])   [.house])|@tsv' test.json