How to format a JSON string as a table using jq?

Question

I need to convert a JSON string using jq, as shown below, into a table format for displaying the output in the terminal.

{
  "results": [
    [
      {
        "field": "@timestamp",
        "value": "2023-07-03 08:28:00.000"
      },
      {
        "field": "CpuReserved",
        "value": "8192.0"
      },
      {
        "field": "CpuUtilized",
        "value": "4056.412942708333"
      },
      {
        "field": "MemoryReserved",
        "value": "61440"
      },
      {
        "field": "MemoryUtilized",
        "value": "4311"
      },
      {
        "field": "@ptr",
        "value": "CpABClUKUTE0NjcxNzAzNzI0NzovYXdzL2Vjcy9jb250YWluZXJpbnNpZ2h0cy9pcmwtaW5mcmEtc2hhcmVkLWplbmtpbnMtbWFpbi9wZXJmb3JtYW5jZRABEjUaGAIGSaxosQAAAAAd+TFeAAZKKHIwAAABEiABKIDF9taRMTDA7v3WkTE4BkC9NEjNPFCBIRgAEAAYAQ=="
      }
    ],
    [
      {
        "field": "@timestamp",
        "value": "2023-07-03 08:28:00.000"
      },
      {
        "field": "CpuReserved",
        "value": "8192.0"
      },
      {
        "field": "CpuUtilized",
        "value": "4056.412942708333"
      },
      {
        "field": "MemoryReserved",
        "value": "61440"
      },
      {
        "field": "MemoryUtilized",
        "value": "4311"
      },
      {
        "field": "@ptr",
        "value": "CpABClUKUTE0NjcxNzAzNzI0NzovYXdzL2Vjcy9jb250YWluZXJpbnNpZ2h0cy9pcmwtaW5mcmEtc2hhcmVkLWplbmtpbnMtbWFpbi9wZXJmb3JtYW5jZRABEjUaGAIGSaxosQAAAAAd+TFeAAZKKHIwAAABEiABKIDF9taRMTDA7v3WkTE4BkC9NEjNPFCBIRgAEAEYAQ=="
      }
    ]
  ]
}

What I want to display in the terminal is as follows:

@timestamp                CpuReserved  CpuUtilized         MemoryReserved   MemoryUtilized  
==========================================================================================
2023-07-03 08:16:00.000   8192.0       410.5300065104166   61440            1417
2023-07-03 08:15:00.000   8192.0       702.310791015625    61440            792

Can someone guide me in the right direction?

Iain Samuel McLean Elder · Accepted Answer · 2023-07-09 12:13:10Z

Maybe:

$ jq -c '.results[]|map(.key=.field)|from_entries|del(."@ptr")' file.json |
   mlr --ijson --opprint --barred cat
+-------------------------+-------------+-------------------+----------------+----------------+
| @timestamp              | CpuReserved | CpuUtilized       | MemoryReserved | MemoryUtilized |
+-------------------------+-------------+-------------------+----------------+----------------+
| 2023-07-03 08:28:00.000 | 8192.0      | 4056.412942708333 | 61440          | 4311           |
| 2023-07-03 08:28:00.000 | 8192.0      | 4056.412942708333 | 61440          | 4311           |
+-------------------------+-------------+-------------------+----------------+----------------+

Without --barred, that looks like:

@̲t̲i̲m̲e̲s̲t̲a̲m̲p̲              C̲p̲u̲R̲e̲s̲e̲r̲v̲e̲d̲ C̲p̲u̲U̲t̲i̲l̲i̲z̲e̲d̲       M̲e̲m̲o̲r̲y̲R̲e̲s̲e̲r̲v̲e̲d̲ M̲e̲m̲o̲r̲y̲U̲t̲i̲l̲i̲z̲e̲d̲
2023-07-03 08:28:00.000 8192.0      4056.412942708333 61440          4311
2023-07-03 08:28:00.000 8192.0      4056.412942708333 61440          4311

You could also pipe the output of that jq command to vd -f json (VisiData) instead of mlr (Miller) to get an interactive table viewer.

Those use jq to extract the information and mlr only to format the table. There is some overlap between the feature set of jq and that of mlr. For instance, you could also remove the @ptr column with mlr's cut:

jq -c '.results[]|map(.key=.field)|from_entries' file.json |
  mlr --ijson --opprint cut -xf @ptr

That jq command, broken down and commented:

jq -c '
  .results[] | # iterate over the elements of the .results array 
               # (which are also arrays)

  map(.key=.field) | # for each of those arrays, transform the
                     # elements (which are objects) by adding a 
                     # field of key "key" with same value as that
                     # with "field" key in each, as that's what
                     # from_entries needs

  from_entries | # transforms those [{"key":"foo","value":"bar"}]
                 # (the "field" field is ignored) to {"foo":"bar"}

  del(."@ptr") # deletes the field with key "@ptr" from those
               # objects' file.json

The result is not JSON, but several JSONs concatenated together, but both jq and mlr support that. With -c (compact), that's NDJSON (newline-delimited JSON) where we have one JSON per line, also supported by vd. To get proper JSON, we'd need:

jq -c '.results|map(map(.key=.field)|from_entries|del(."@ptr"))' file.json

Where we use map on the .results array so it results in another JSON array instead of iterating over the elements. So the end result is one large arrays. That's also supported by jq (obviously as that's proper JSON), mlr and vd, is a bit longer to type and means those tools need to read up to the closing ] at the very end before they have anything to chew on. In practice, I've not checked whether that made any difference in terms of performance though.

Thanks for sharing Miller! I'll try it next. I currently use jtbl -m (jtbl) for my tabulation. — Iain Samuel McLean Elder, Commented Jul 9, 2023 at 11:59

kev · Accepted Answer · 2023-07-07 09:54:38Z

4

Yet another:

$ jq -r '.results|map(map({key:.field,value}|select(.key!="@ptr"))|from_entries)|(.[0]|keys_unsorted) as $keys|([$keys]+map([.[$keys[]]]))[]|@csv' input.json | xsv table
@timestamp               CpuReserved  CpuUtilized        MemoryReserved  MemoryUtilized
2023-07-03 08:28:00.000  8192.0       4056.412942708333  61440           4311
2023-07-03 08:28:00.000  8192.0       4056.412942708333  61440           4311

Notes:

from_entries will convert an array of key-value pairs to an object.
To convert array-of-objects to csv: View Details.
xsv table is for pretty-printing only, you can remove it.

edited Jul 7, 2023 at 9:54

answered Jul 7, 2023 at 9:29

kev

9861 gold badge6 silver badges13 bronze badges

1

I like this answer most since it uses jq only (no mlr) and works without explicit indices.
– Hermann
Commented Jul 7, 2023 at 10:15

Add a comment |

Velin Budinov · Accepted Answer · 2023-07-07 09:54:30Z

I think I found the answer:

jq -r '([ "@timestamp", "CpuReserved", "CpuUtilized", "MemoryReserved", "MemoryUtilized"] | (., map(length*"-"))), ( .results[] | [.[0,1,2,3,4].value] ) | @tsv' |column -ts $'\t'
@timestamp               CpuReserved  CpuUtilized         MemoryReserved  MemoryUtilized
----------               -----------  -----------         --------------  --------------
2023-07-03 08:28:00.000  8192.0       4056.412942708333   61440           4311
2023-07-03 08:28:00.000  8192.0       4056.412942708333   61440           4311
2023-07-03 08:27:00.000  8192.0       1056.0744270833331  61440           4436
2023-07-03 08:27:00.000  8192.0       1056.0744270833331  61440           4436
2023-07-03 08:26:00.000  8192.0       2756.6764583333334  61440           5138
2023-07-03 08:26:00.000  8192.0       2756.6764583333334  61440           5138
2023-07-03 08:25:00.000  8192.0       5715.494895833333   61440           6600
2023-07-03 08:25:00.000  8192.0       5715.494895833333   61440           6600
2023-07-03 08:24:00.000  8192.0       7977.704166666666   61440           8451
2023-07-03 08:24:00.000  8192.0       7977.704166666666   61440           8451
2023-07-03 08:23:00.000  8192.0       7288.051666666666   61440           4757
2023-07-03 08:23:00.000  8192.0       7288.051666666666   61440           4757
2023-07-03 08:22:00.000  8192.0       4286.02375          61440           4815
2023-07-03 08:22:00.000  8192.0       4286.02375          61440           4815
2023-07-03 08:21:00.000  8192.0       3357.523776041666   61440           2146
2023-07-03 08:21:00.000  8192.0       3357.523776041666   61440           2146
2023-07-03 08:20:00.000  8192.0       990.2647916666666   61440           1692
2023-07-03 08:20:00.000  8192.0       990.2647916666666   61440           1692
2023-07-03 08:19:00.000  8192.0       4533.409375         61440           1816
2023-07-03 08:19:00.000  8192.0       4533.409375         61440           1816
2023-07-03 08:18:00.000  8192.0       939.4855208333333   61440           1810
2023-07-03 08:18:00.000  8192.0       939.4855208333333   61440           1810
2023-07-03 08:17:00.000  8192.0       4770.659791666667   61440           1924
2023-07-03 08:17:00.000  8192.0       4770.659791666667   61440           1924
2023-07-03 08:16:00.000  8192.0       410.5300065104166   61440           1417
2023-07-03 08:16:00.000  8192.0       410.5300065104166   61440           1417
2023-07-03 08:15:00.000  8192.0       702.310791015625    61440           792
2023-07-03 08:15:00.000  8192.0       702.310791015625    61440           792
2023-07-03 08:14:00.000  8192.0       0.0                 61440           0
2023-07-03 08:14:00.000  8192.0       0.0                 61440           0

It's good to hear that you found the a solution. You could make your answer more helpful for future readers by explaining how it works and why it's better than the earlier answers. — Toby Speight, Commented Jul 7, 2023 at 12:25

glenn jackman · Accepted Answer · 2023-07-07 14:09:01Z

2

Another take that uses jq to emit tab-separated values, and column to prettify it

jq -r '
  .results
  | first as $first
  | [$first | map(.field)] + [.[] | map(.value)]
  | map(.[:-1])[]
  | @tsv
' file.json | column -t -s $'\t'

@timestamp               CpuReserved  CpuUtilized        MemoryReserved  MemoryUtilized
2023-07-03 08:28:00.000  8192.0       4056.412942708333  61440           4311
2023-07-03 08:28:00.000  8192.0       4056.412942708333  61440           4311

answered Jul 7, 2023 at 14:09

glenn jackman

87.1k16 gold badges120 silver badges173 bronze badges

Add a comment |

Stack Exchange Network

How to format a JSON string as a table using jq?

4 Answers 4

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
json
jq
.

Hot Network Questions

How to format a JSON string as a table using jq?

4 Answers 4

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged jsonjq.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
json
jq
.