How to extract json data and output to csv using jq

Question

My json array file is one long file and has many objects [{},{},{},{}], within each object I need the value for three keys: "STK_NUM":"1004251 ", "DLR_COST":40.32 , "RTL_AMT":9.99. How would I write the jq program to get the key names as headers in my csv file: STK_NUM, DLR_COST,RTL_AMT and the values of these keys from all objects: 1004251,40.32,9.99 ? These three keys are in every object.

Desired csv:

STK_NUM,DLR_COST,RTL_AMT
1004251,40.32,9.99
1012658,29.99,4.69
1232556,18.89,2.49

I've tried:

jq -r .[].STK_NUM HSEitm.json

result:

"1004251"
"1012658"
"1232556"

All my other jq script attempts result in errors because I don't know what I'm doing.

If anyone could show me I would be very grateful.

peak · Accepted Answer · 2021-10-11 03:35:45Z

1

In the spirit of DRY:

jq -r '
  ["STK_NUM", "DLR_COST", "RTL_AMT"] as $headers
  | $headers, 
    # Rows:
    map(.[$headers[]]) 
  | @csv
'

answered Oct 11, 2021 at 3:35

peak

119k21 gold badges185 silver badges218 bronze badges

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

Comments

pmf · Accepted Answer · 2021-10-11 02:39:33Z

0

Try

jq -r '
  
  # Headers
  ["STK_NUM", "DLR_COST", "RTL_AMT"],
  
  # Rows
  (.[] | [.STK_NUM, .DLR_COST, .RTL_AMT])
  
  # Output Format
  | @csv
  
' HSEitm.json

answered Oct 11, 2021 at 2:39

pmf

38.6k3 gold badges31 silver badges41 bronze badges

Collectives™ on Stack Overflow

How to extract json data and output to csv using jq

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related