Parse json output using jq

Question

Firstly,thank you so much for your support.

I have trouble parsing a json output into csv format. I was able to get some kind of output but that does not meet the expectation.

Curl Command output

{
    "results": [{
            "name": "smith Jones",
            "DOB": "1992-03-26",
            "Enrollmentdate": "2013-08-24"

        },

        {
            "name": "Jacob Mathew",
            "DOB": "1993-03-26",
            "Enrollmentdate": "2014-10-02"
        },

        {
            "name": "Anita Rodrigues",
            "DOB": "1994-03-26",
            "Enrollmentdate": "2015-02-19"
        }
    ]
}

JQ commond used

<curl-command>|jq '.results | map(.name), map(.DOB), map(.Enrollmentdate) | @csv' >file.csv

My Output

smith jones, Jacob Mathew, Anita Rodrigues
1992-03-26, 1993-03-26, 1994-03-26
2013-08-24, 2014-10-02, 2015-02-19

This might not be csv exactly but below is the expected output.

Name                 DOB             Enrollmentdate
smith jones,       1992-03-26,        2013-08-24
jacob Mathew,      1993-03-26,        2014-10-02
Anitha Rodrigues,  1994-03-26,        2015-02-19

peak · Accepted Answer · 2019-08-15 16:36:41Z

2

With your sample JSON, the invocation:

jq -r '
  .results[]
  | [.name, .DOB, .Enrollmentdate ]
  | @csv'

produces:

"smith Jones","1992-03-26","2013-08-24"
"Jacob Mathew","1993-03-26","2014-10-02"
"Anita Rodrigues","1994-03-26","2015-02-19"

If you don't want the (in this case) superfluous quotation marks, you could replace @csv by join(",") but it would be better to write a simple filter to take care of values with commas, etc.

Headers

If the first result has the keys in the correct order, you could get away with:

  (.results[0] | keys_unsorted),
  (.results[]
  | [.name, .DOB, .Enrollmentdate ])
  | @csv

But it would probably be better to use a bit of jq magic along the lines of the following:

  (.results[0] | keys_unsorted) as $headers
  | $headers,
    (.results[]
     | [getpath($headers[]|[.])])
  | @csv

edited Aug 15, 2019 at 16:36

answered Aug 15, 2019 at 2:37

peak

119k21 gold badges185 silver badges218 bronze badges

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

1 Comment

user3686627 Over a year ago

Thank You! this worked like a charm :) but how do i print the key names(name, DOB, Enrollmentdate)? I can write a bash script to generate it but was wondering if JQ could solve it.

Collectives™ on Stack Overflow

Parse json output using jq

1 Answer 1

Headers

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Headers

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related