Nested array in JSON to different rows in CSV

Question

I have the following JSON:

{
  "transmitterId": "30451155eda2",
  "rssiSignature": [
    {
      "receiverId": "001bc509408201d5",
      "receiverIdType": 1,
      "rssi": -52,
      "numberOfDecodings": 5,
      "rssiSum": -52
    },
    {
      "receiverId": "001bc50940820228",
      "receiverIdType": 1,
      "rssi": -85,
      "numberOfDecodings": 5,
      "rssiSum": -85
    }
  ],
  "timestamp": 1574228579837
}

I want to convert it to CSV format, where each row corresponds to an entry in rssiSignature (I have added the header row for visualization purposes):

timestamp,transmitterId,receiverId,rssi
1574228579837,"30451155eda2","001bc509408201d5",-52
1574228579837,"30451155eda2","001bc50940820228",-85

My current attempt is the following, but I get a single CSV row:

$ jq -r '[.timestamp, .transmitterId, .rssiSignature[].receiverId, .rssiSignature[].rssi] | @csv' test.jsonl
1574228579837,"30451155eda2","001bc509408201d5","001bc50940820228",-52,-85

How can I use jq to generate different rows for each entry of the rssiSignature array?

thanasisp · Accepted Answer · 2020-10-13 03:01:03Z

2

In order to reuse a value of the upper level, like the timestamp, for every item of the rssiSignature array, you can define it as a variable. You can get your csv like this:

jq -r '.timestamp as $t | .transmitterId as $tid |
       .rssiSignature[] | [ $t, $tid, .receiverId, .rssi] | @csv
      ' file.json

Output:

1574228579837,"30451155eda2","001bc509408201d5",-52
1574228579837,"30451155eda2","001bc50940820228",-85

Also here is an way to print headers for an output file in bash, independent of what commands we call, using commands grouping.

(
printf "timestamp,transmitterId,receiverId,rssi\n"
jq -r '.timestamp as $t | .transmitterId as $tid |
       .rssiSignature[] | [ $t, $tid, .receiverId, .rssi] | @csv
      ' file.json
) > output.csv

edited Oct 13, 2020 at 3:01

answered Oct 13, 2020 at 1:24

thanasisp

5,9953 gold badges17 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

peak · Accepted Answer · 2020-10-13 02:41:05Z

1

Actually, the task can be accomplished without the use of any variables; one can also coax jq to include a header:

jq -r '
  ["timestamp","transmitterId","receiverId","rssi"],
   [.timestamp, .transmitterId] + (.rssiSignature[] | [.receiverId,.rssi])
  | @csv'

A single header with multiple files

One way to produce a single header with multiple input files would be to use inputs in conjunction with the -n command-line option. This happens also to be efficient:

jq -nr '
["timestamp","transmitterId","receiverId","rssi"],
 (inputs |
  [.timestamp, .transmitterId] + (.rssiSignature[] | [.receiverId,.rssi]))
| @csv'

edited Oct 13, 2020 at 2:41

answered Oct 13, 2020 at 1:40

peak

119k21 gold badges185 silver badges218 bronze badges

4 Comments

thanasisp Over a year ago

@kmundnic I 'd use this, also prints the headers easily. In case you had some longer paths until your array, I guess it would be better to define variables.

user2317421 Over a year ago

@thanasisp I tested both answers and yours is slightly faster for my use case (reading huge JSONL files and translating them to CSV files). I have ~300 files with up to 21M rows each, so time matters :-)

user2317421 Over a year ago

@peak is there a way using jq to preprend the header only once if I am reading several JSONL instead of a single JSON from a file? I can do this in bash, just wondering if there's an option in jq.

peak Over a year ago

kmundnic - See update. @thanasisp - Tx for attending to the -n bug.

Collectives™ on Stack Overflow

Nested array in JSON to different rows in CSV

2 Answers 2

Comments

A single header with multiple files

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

A single header with multiple files

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related