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

Question

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

Answer 1

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