CODEWITHSUNDEEP
jsonexport-to-csvjq
user3686627
user3686627

Reputation: 21

Parse json output using jq

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

Upvotes: 0

Views: 58

Answers (1)

peak
peak

Reputation: 116957

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

Upvotes: 2

Related Questions