0

I have a JSON payload that I'm looking to convert to CSV that looks like the below:

[
  {
    "endpoint": "APPLE",
    "date": "2022-11-02 12:00",
    "upUsage": 0,
    "downUsage": 18000,
    "upAvgRate": 0,
    "downAvgRate": 600,
    "upMaxRate": 0,
    "downMaxRate": 800
  },
  {
    "endpoint": "BANANA",
    "date": "2022-11-02 12:00",
    "upUsage": 0,
    "downUsage": 17600,
    "upAvgRate": 0,
    "downAvgRate": 587,
    "upMaxRate": 0,
    "downMaxRate": 693
  },
  {
    "endpoint": "CARROT",
    "date": "2022-11-02 12:00",
    "upUsage": 0,
    "downUsage": 8000,
    "upAvgRate": 0,
    "downAvgRate": 533,
    "upMaxRate": 0,
    "downMaxRate": 533
  }
]

I am trying to convert this to a standard CSV file with the appropriate headers via jq, but having difficulties in doing so. Below is my desired output:

"endpoint","date","upUsage","downUsage","upAvgRate","downAvgRate","upMaxRate","downMaxRate"
"APPLE","2022-11-02 12:00",0,18000,0,600,0,800
"BANANA","2022-11-02 12:00",0,17600,0,587,0,693
"CARROT","2022-11-02 12:00",0,8000,0,533,0,533

I've been able to use the below jq to get close to this output, but my headers are not being included:

cat testJson.json | jq -r '.[] | join(",")'

*Note: - There are also instances in which one of my JSON objects may not include the same number of values, so I need my output file to account for this and simply enter a null value between the commas to keep a consistent number of columns

Drew
  • 33
  • 3
  • 1
    Does this answer your question? [How to convert arbitrary simple JSON to CSV using jq?](https://stackoverflow.com/questions/32960857/how-to-convert-arbitrary-simple-json-to-csv-using-jq) – 0stone0 Nov 02 '22 at 16:35
  • The command you've tried does nothing with the headers. Please take a look at the marked duplicate, that should be just fine to convert your json to csv. – 0stone0 Nov 02 '22 at 16:36

1 Answers1

1

Assuming the keys are consistently ordered within the objects:

jq -r '((first | keys_unsorted), (.[] | to_entries | map(.value))) | @csv' file.json

"endpoint","date","upUsage","downUsage","upAvgRate","downAvgRate","upMaxRate","downMaxRate"
"APPLE","2022-11-02 12:00",0,18000,0,600,0,800
"BANANA","2022-11-02 12:00",0,17600,0,587,0,693
"CARROT","2022-11-02 12:00",0,8000,0,533,0,533
peak
  • 105,803
  • 17
  • 152
  • 177
glenn jackman
  • 238,783
  • 38
  • 220
  • 352
  • 1
    Could you explain why this needs an answer, instead of closing as duplicate? – 0stone0 Nov 02 '22 at 16:50
  • 1
    Because I worked out an answer before I read the comments, so I shared it. Why do you need an explanation? – glenn jackman Nov 02 '22 at 16:53
  • 1
    @GlennJackman - I believe the question is whether you found anything in the Q or your A that is not already covered by an existing SO Q&A. – peak Nov 02 '22 at 18:47
  • 1
    This assumes the fields of every object is in the same order, but JSON defines the order meaningless. This program could very well not work, or it could stop working at any time. – ikegami Nov 03 '22 at 05:03
  • Just out of curiosity, I assumed I was missing something @glennjackman. – 0stone0 Nov 03 '22 at 10:49