Convert Array of JSON Objects to CSV With Headers

Question

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

Does this answer your question? How to convert arbitrary simple JSON to CSV using jq? — 0stone0
– 0stone0, Commented Nov 2, 2022 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
– 0stone0, Commented Nov 2, 2022 at 16:36

peak · Accepted Answer · 2022-11-03 02:18:16Z

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

edited Nov 3, 2022 at 2:18

peak

119k21 gold badges185 silver badges218 bronze badges

answered Nov 2, 2022 at 16:43

glenn jackman

249k42 gold badges233 silver badges362 bronze badges

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

5 Comments

0stone0 Over a year ago

Could you explain why this needs an answer, instead of closing as duplicate?

glenn jackman Over a year ago

Because I worked out an answer before I read the comments, so I shared it. Why do you need an explanation?

peak Over a year ago

@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.

ikegami Over a year ago

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.

0stone0 Over a year ago

Just out of curiosity, I assumed I was missing something @glennjackman.

Collectives™ on Stack Overflow

Convert Array of JSON Objects to CSV With Headers

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related