0

I can't seem to figure out how to use jq to turn my data into a csv. I have the following json:

    {
  "id": 2,
  "type": "message",
  "date": "2010-01-11T01:19:45",
  "edited": "1969-12-31T19:00:00",
  "from": "USER1",
  "from_id": 5514,
  "text": "Message1"
 },
 {
  "id": 3,
  "type": "message",
  "date": "2010-01-11T01:19:28",
  "edited": "1969-12-31T19:00:00",
  "from": "USER2",
  "from_id": 5515,
  "text": "Message2"
 },
 {
  "id": 4,
  "type": "message",
  "date": "2010-01-11T01:19:28",
  "edited": "1969-12-31T19:00:00",
  "from": "USER1",
  "from_id": 5514,
  "text": "Message3"
 },

I'm trying to get the date, from, and text from the json and to output it in a csv that looks like:

From,Date,Text
USER1,2010-01-11T01:19:45,Message1
USER2,2010-01-11T01:19:28,Message2
USER1,2010-01-11T01:19:28,Message3

Is there a way to do this with jq or would something different be better to use?

2 Answers 2

1

It's

<input.json jq --raw-output '.[]|[.from,.date,.text]|@csv'
0

If you want to be able to dynamically select the keys that you want to extract, and also have those keys being used in the CSV header of the output document, you may pass them on the command line to jq like so:

jq -r '$ARGS.positional, map([.[$ARGS.positional[]]])[] | @csv' file --args date from

This passes date and from to the jq expression. In the expression, these strings will be found in the $ARGS.positional array. The expression uses this array first to create an array of header labels (the keys themselves). Then it uses the array elements as keys to extract the corresponding values from the input data in file. The header and data is then converted into properly quoted CSV output using the @csv operator.

The above command would generate the following output given the data in the question (assuming the JSON fragment is put into an array and the last comma is deleted):

"date","from"
"2010-01-11T01:19:45","USER1"
"2010-01-11T01:19:28","USER2"
"2010-01-11T01:19:28","USER1"

With --args from date text in place of --args date from, you'll get

"from","date","text"
"USER1","2010-01-11T01:19:45","Message1"
"USER2","2010-01-11T01:19:28","Message2"
"USER1","2010-01-11T01:19:28","Message3"

jq always quotes all CSV output fields.


You may easily also get the same sort of output using Miller (mlr):

$ mlr --j2c cut -f from,date,text file
date,from,text
2010-01-11T01:19:45,USER1,Message1
2010-01-11T01:19:28,USER2,Message2
2010-01-11T01:19:28,USER1,Message3

Here, we ask mlr to cut out (extract) the fields called from, date and text. The input is converted from JSON to CSV using the single option --j2c.

Miller will only quote CSV fields that need quoting.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .