5

Is it possible to convert this json:

[
    {
        "bytes": 276697,
        "checked": false
    },
    {
        "bytes": 276697,
        "checked": false
    }
]

to a table WITH headers in jq?

I've tried:

cat file.json | jq '.[] | join(",")'

but it omits headers:

"276697,false"
"276697,false"

it should be:

"bytes,checked"
"276697,false"
"276697,false"

I hoped that you can just run two commands:

cat file.json | jq '.[] | keys, .[] | join(",")'

but the second one fails:

"bytes,checked"
jq: error (at <stdin>:64): Cannot iterate over null (null)

Ideally it would be simpler than this.

Improve this question
2

2 Answers 2

Reset to default
12

You may pick the headers as an array of strings from the keys of the first element in the list, then extract all elements' values as separate arrays. Applying the @csv output operator to each element of the resulting list will CSV-quote the data (jq quotes all strings, but not booleans or numbers):

$ jq -r '[first|keys_unsorted] + map([.[]]) | .[] | @csv' file
"bytes","checked"
276697,false
276697,false

Or,

$ jq -r '(first|keys_unsorted), (.[]|[.[]]) | @csv' file
"bytes","checked"
276697,false
276697,false

Or,

$ jq -r '(first|keys_unsorted), map(map(.))[] | @csv' file
"bytes","checked"
276697,false
276697,false

Or any other way to extract the values into separate arrays.

Note that this relies on the keys to occur in the same order all throughout the input data.

However, it's even easier with Miller (mlr):

$ mlr --j2c cat file
bytes,checked
276697,false
276697,false

This simply passes the data through Miller's cat command (which, when used like this, does not modify the data), while converting from JSON to CSV using the --j2c option (short for --ijson --ocsv). Note that since Miller is properly CSV-aware, it only quotes the fields that actually need quoting.

You may also get a nicely formatted table by choosing the pretty-printed output format together with --barred:

$ mlr --j2p --barred cat file
+--------+---------+
| bytes  | checked |
+--------+---------+
| 276697 | false   |
| 276697 | false   |
+--------+---------+

(--j2p is short for --ijson --opprint.)

Or without --barred:

$ mlr --j2p cat file
bytes  checked
276697 false
276697 false
Improve this answer
1
  • Amazing, thank you. I haven't tested all the examples yet, but miller blew me away already. So much simpler. I knew that someone must have built a tool to solve this in a much simpler way already. Commented Aug 25, 2023 at 19:51
2

I got it:

cat file.json | jq '(.[0] | keys), .[] | join(",")'

looks like you can surround any part in parentheses to prevent it from "consuming" the stream (I don't know if that's what it's called or even if I got it right here, because I couldn't find anything on jq's documentation and had to piece everything together with bits and bobs scattered throughout various blogs and stackoverflows, so if there's a "proper" way to do it please let me know).

By the way if you have a shell for loop like I did use -s option to combine separate json objects:

$ for i in {3,4,5,8} 
do rclone rc core/stats --rc-user USER --rc-pass PASS --rc-addr :557$i
done | jq -rs '(.[0] | keys), .[] | join(",")' | column -ts,

bytes      checks  deletedDirs  deletes  elapsedTime       errors  eta  fatalError  renames  retryError  speed               totalBytes  totalChecks  totalTransfers  transferTime  transfers
1660182    0       0            0        258038.009782457  0       0    false       0        false       1664.9322505627426  1660182     0            6               0             6
407752609  0       0            0        258038.054874325  0       0    false       0        false       10615.04533495996   407752609   0            86              0             86
7403585    0       0            0        258038.103563555  0       0    false       0        false       20892.381593377457  7403585     0            2               0             2
0          0       0            0        258038.156466825  0            false       0        false       0                   0           0            0               0             0
Improve this answer
0

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.